USERNAME

[hakuna12]

Hi everyone,
I have just recently started and also I am learning on my own, the VBA code for Macros in Excel.

I just thought, I'll get my problem solved if and only if I share it with scholars over here.

I have a code within a sheet which generates Username when activecell is target. Everything is fine. But, when I run the macro UPDATE to update and refresh the workbook, one entire column is filled with USERNAME!!! I tried so many things, but i am not able to debug it effectively.

private sub worksheet_change(byval target as range)
...
...
if target.cells.column = 11 then
.offset(0,13).value = application.username

This is within a sheet. The refresh/update code is on the main worksheet.

Please help me!

 

[Hoaokapohaku]

I can't answer your question for sure because you didn't post enough of the code, and even what you did post seems to be missing part of it. What is the range for ".offset(0,13).value?"

This code works to accomplish what I think you are trying to accomplish. Note addition of "ActiveCell":

Private Sub worksheet_change(ByVal target As Range)
    If target.Cells.Column = 11 Then ActiveCell.Offset(0, 13).Value = Application.UserName
End Sub

This code duplicates the erroneous results you are getting. Note addition of "EntireColumn":

Private Sub worksheet_change(ByVal target As Range)
    If target.Cells.Column = 11 Then ActiveCell.Offset(0, 13).EntireColumn.Value = Application.UserName
End Sub

 

[Hoaokapohaku]

What are you referring to by "The refresh/update code is on the main worksheet?" Given the worksheet_change event, nothing is needed anywhere else to execute this code. Anytime any cell in the target range (column 11) is changed, the code will run.

 

[hakuna12]

I have a code in a sheet which works only when anything in COLUMN 11 changes.

Range is ("X2:X2000") ---> column X will have the USER's name.
And this happens when something in column K is changed. (.offset(0,13) ------> offset from column K, is column X )


// What are you referring to by "The refresh/update code is on the main worksheet?" Given the worksheet_change event, nothing is needed anywhere else to execute this code. Anytime any cell in the target range (column 11) is changed, the code will run. //

You are right. Thus my problem is, when nothing is being changed during the refresh of the workbook in THIS sheet, Column X is getting triggered and entire COLUMN is filled by USER'S NAME. WHICH IS WRONG!

Please help! Hope this info was fine to help...

 

[hakuna12]

The above codes, don't stop from getting triggered.

i need to set up a flag so that while the workbook is refreshing, no change should be triggered in THIS SHEET.

 

[hakuna12]

MAINLY I HAVE TO DISABLE THIS PRIVATE MACRO WHILE THE OTHER MACRO IS RUNNING.

IS IT POSSIBLE...?????????//

 

[IDS]

Put EnableEvents = False
at the start of the other macro and
EnableEvents = True
at the end.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[hakuna12]

It's already done. But still, I am not able to stop the SHEET macro from triggering. :'(

 

[hakuna12]

IS THERE A POSSIBILITY OF CREATING A MACRO INSTEAD OF AN EVENT IN THE SHEET????????

 

[Hoaokapohaku]

If you followed IDS's advice, and you still have the problem, then your other macro is not directly causing the event to fire. My guess is that for some reason, some cells in column 11 in the sheet with the event have formulas, and that something is triggering the workbook to recalculate after the macro completes in a manner that changes one or more of those values. The recalculation changes the value in the target column and therefore triggers the event. I'm not sure about this, but if your workbook is very calculation intensive, perhaps a recalculation begins during the macro, but doesn't complete until after the macro is finished, thereby triggering the event. Honestly, I don't think this is the case, but it's easy to prove or disprove. Right before the line IDS suggested, "EnableEvents = True" add the line "Application.Calculate" and see if that eliminates the problem. If it does, and if the macro doesn't need the workbook to recalculate in order to properly function, I'd add, "Application.calculation = xlmanual" near the beginning of the macro, and "Application.calculation = xlautomatic" Actually, if a workbook is calculation intensive, I usually do this just to speed up my macros.

If the above doesn't solve it, I'd start looking for event procedures that I might have put someplace by accident that could be triggering changes to the target column and triggering that event.

Can you just upload the workbook? This would probably be resolved a lot faster if you did.

 

[Hoaokapohaku]

Yes, you can put a macro in a module instead of on the sheet, but it won't automatically execute when a value in column 11 is changed. You would have to find some other way to trigger the macro, such as running it manually, putting a control on the sheet that triggers the macro, etc.