Problem with VBA code....

[RandyD123]

In the code below I am having an issue with some of the coding. I want the user name to show in column B and I want column B to clear if the user deletes or clears the info in column A. But at the same time I don't want the user to clear column B if there is an entry in column A.

Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo Catch

'// Prevent any changes firing this event again
Application.EnableEvents = False

'// Change the '1' to ignore any heading rows
1 If Target.Row > 1 Then

'// Which column was changed?
Select Case Target.Column
'// A
Case 1
Target.Offset(, 1).Value = "Changed By " & UserName & " on " & Date & " @ " & Time
Case 2
'// Prevent user changes to Col B
Application.Undo
Beep
End Select

End If

Catch:

'// Make sure event handling turned on again
Application.EnableEvents = True

End Sub

Any help would be much appreciated. Thank You

 

[dmt32]

Hi,
try updating this line

Target.Offset(, 1).Value = "Changed By " & UserName & " on " & Date & " @ " & Time

to this

Target.Offset(, 1).Value = IIf(Len(Target.Value), "Changed By " & Application.UserName & " on " & Date & " @ " & Time, "")

and see if does what you want

Dave

 

[RandyD123]

Hi,
try updating this line

Target.Offset(, 1).Value = "Changed By " & UserName & " on " & Date & " @ " & Time

to this

Target.Offset(, 1).Value = IIf(Len(Target.Value), "Changed By " & Application.UserName & " on " & Date & " @ " & Time, "")

and see if does what you want

Dave

Nope, I gives me the name of my company, not the user who made the change.

 

[dmt32]

Nope, I gives me the name of my company, not the user who made the change.

Updated code is based on your post to clear Cell in Column B if Cell in Column A deleted which it should do.

Application.UserName is the user name which is set by the user when they run the program for the first time.
if you go to File > OPTIONS > GENERAL and set it under User Name. Clearly, it has been set with your company name

Change Application.UserName

to

Envirion("UserName")

which should show users network name

Dave

 

[RandyD123]

Updated code is based on your post to clear Cell in Column B if Cell in Column A deleted which it should do.

Application.UserName is the user name which is set by the user when they run the program for the first time.
if you go to File > OPTIONS > GENERAL and set it under User Name. Clearly, it has been set with your company name

Change Application.UserName

to

Envirion("UserName")

which should show users network name

Dave

It errors out on Envirion (sub or function not defined)

 

[RandyD123]

Updated code is based on your post to clear Cell in Column B if Cell in Column A deleted which it should do.

I don't see that in "updated" code?

And thanks for trying to help.....

 

[dmt32]

sorry, typo

Environ("UserName")

Dave

 

[RandyD123]

sorry, typo

Environ("UserName")

Dave

Seems to be working perfectly. Thank You!!!

 

[dmt32]

Seems to be working perfectly. Thank You!!!

welcome - thanks for feedback

Dave