×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

USERNAME

USERNAME

USERNAME

(OP)
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. pc3

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!!! mad 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!

RE: USERNAME

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":

CODE -->

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":

CODE -->

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

RE: USERNAME

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.

RE: USERNAME

(OP)
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...

RE: USERNAME

(OP)
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.

RE: USERNAME

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

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

RE: USERNAME

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/

RE: USERNAME

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

RE: USERNAME

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

RE: USERNAME

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.

RE: USERNAME

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close