Auto Insert Username and Date Stamp on Specific cell value from Drop-down.

[Jeet_Dhillon]

Hello Friends,
I am using this code for auto inserting date and username when a cell value is changed.
I need when "Done" is selected from the dropdown list,auto-insert should trigger, but now auto-insert is for all the Values.
Please share some suggestions
thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
If Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Or Target.Column = 8 And Target.Value <> "" Then
Application.EnableEvents = False
Target.Offset(0, 0) = Format(Now(), "dd-mm-yy/") & Environ("UserName")
Application.EnableEvents = True
End If
Handler:
End Sub

 

[DanteAmor]

How about:

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo Handler
  If Target.CountLarge > 1 Then Exit Sub
  If (Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Or Target.Column = 8) _
    And LCase(Target.Value) = LCase("Done") Then
    Application.EnableEvents = False
    Target.Value = Format(Now(), "dd-mm-yy/") & Environ("UserName")
    Application.EnableEvents = True
  End If
Handler:
  Application.EnableEvents = True
End Sub

 

[Jeet_Dhillon]

How about:

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo Handler
  If Target.CountLarge > 1 Then Exit Sub
  If (Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Or Target.Column = 8) _
    And LCase(Target.Value) = LCase("Done") Then
    Application.EnableEvents = False
    Target.Value = Format(Now(), "dd-mm-yy/") & Environ("UserName")
    Application.EnableEvents = True
  End If
Handler:
  Application.EnableEvents = True
End Sub

[/QUOTE

Thanks for Quick Reply, I tried but code not working auto-insert stopped working.

 

[DanteAmor]

You put this in a module and run it

sub test()
Application.EnableEvents = True
end sub

Try again

 

[Jeet_Dhillon]

You put this in a module and run it

sub test()
Application.EnableEvents = True
end sub

Try again

Sorry, I think I am losing you here.
this is the only VBA code in the entire workbook and in the worksheet, there is no conditional formatting.

 

[DanteAmor]

Thanks for Quick Reply, I tried but code not working auto-insert stopped working.

Sorry, I think I am losing you here.
this is the only VBA code in the entire workbook and in the worksheet, there is no conditional formatting.

If the macro does not work, maybe the events are off, the "Application.EnableEvents = True" instruction is to activate the events. This has nothing to do with formats.

Let's start again.

If you write the word "Done" in cell A2, what do you need?
Do you want the word "Done" to be replaced by the date and the user?
Or what do you need?

 

[Jeet_Dhillon]

Yes ,i want only "done" to be replaced by Username and date ,but for all other words no replacement required .

 

[DanteAmor]

Yes ,i want only "done" to be replaced by Username and date ,but for all other words no replacement required .

That makes the code.
You must replace your old code with the new one. Be sure to put the code on the sheet where you write the word "Done"

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo Handler
  If Target.CountLarge > 1 Then Exit Sub
  If (Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Or Target.Column = 8) _
    And LCase(Target.Value) = LCase("Done") Then
    Application.EnableEvents = False
    Target.Value = Format(Now(), "dd-mm-yy/") & Environ("UserName")
    Application.EnableEvents = True
  End If
Handler:
  Application.EnableEvents = True
End Sub

To ensure that the events are active, please put the following code in a module and run it. Then you return to the sheet and put the word "Done" in cell B2.

sub test()
Application.EnableEvents = True
end sub

 

[Jeet_Dhillon]

Sorry, code won't work .i followed exact same procedure.

 

[DanteAmor]

But saying "code won't work" is not enough. It does nothing or sends an error. You can comment

Try this, A message with "Hi" should appear, if it does not appear, it means that your events are not active or the macro is elsewhere, the macro should be in the events of the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
  msgbox "Hi"
  On Error GoTo Handler
  If Target.CountLarge > 1 Then Exit Sub
  If (Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Or Target.Column = 8) _
    And LCase(Target.Value) = LCase("Done") Then
    Application.EnableEvents = False
    Target.Value = Format(Now(), "dd-mm-yy/") & Environ("UserName")
    Application.EnableEvents = True
  End If
Handler:
  Application.EnableEvents = True
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

_______________________________________________________

I share my test file. type "done" in cell B3 and automatically stamp the date and user

Dropbox - File Deleted - Simplify your life

www.dropbox.com