Event trigger VBA routine possible?

[Burrgogi]

Whenever I need to create a new account on a website, I usually have a hard time coming up with a clever password for it on the spot.

In my spare time I have been keeping a list of possible passwords I'd like to use for the future. (And yes, I keep this Excel file password protected at all times).
Col. A contains all the possible ones I've come up with.

When I type "USED" in Col. B, I'd like Col. C to automatically display the current date and save the file.

As an example:
Col. A.................Col. B.....Col C
Password1
Password2
Password3 USED 2020/09/29
Password4

 

[Trebor76]

Hi Burrgogi,

Try this event macro on the sheet in question:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 And StrConv(Target.Value, vbUpperCase) = "USED" Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
                Target.Offset(0, 1).Value = Format(Now(), "YYYY/MM/DD")
            .EnableEvents = True
            .ScreenUpdating = False
        End With
    End If

End Sub

Regards,

Robert

 

[Burrgogi]

I've got it saved in the workbook but it's not working for some reason. Where am I supposed to be saving this code? here's a screenshot of where I have it saved.

https://i.postimg.cc/JhFxmZCF/Untitled-3.png

 

[RoryA]

It needs to be in the module for the worksheet in question, not in ThisWorkbook.

 

[Trebor76]

Try this event macro on the sheet in question

An event macro needs to be put on the sheet it's meant to work with. To do this follow these four steps:

1. Copy the code from thread 2 to the clipboard (Ctrl + C)
2. Right click on the sheet name you want the code to run on and from the shortcut menu select View Code
3. Paste (Ctrl + V) the code from Step 1 into the blank white screen (Visual Basic Editor)
4. From the File menu select Close and Return to Microsoft Excel

Hope that helps,

Robert

 

[Burrgogi]

An event macro needs to be put on the sheet it's meant to work with. To do this follow these four steps:

1. Copy the code from thread 2 to the clipboard (Ctrl + C)
2. Right click on the sheet name you want the code to run on and from the shortcut menu select View Code
3. Paste (Ctrl + V) the code from Step 1 into the blank white screen (Visual Basic Editor)
4. From the File menu select Close and Return to Microsoft Excel

Hope that helps,

Robert

OK so followed those instructions and tried it on the 1st sheet - it worked. However, as you can see from my screenshot, I have roughly 10 worksheets in the file. Are you saying I have to copy/paste 10 times into each of the sheets? Or is there a simpler way? I tried RoryA's suggestion of inserting a module and that did not work.

EDIT:

I have 1 more follow up request if you don't mind. Sometimes I end up changing my mind so I hit the delete key and delete the word "USED" from column B, i'd like the date in column C to be automatically removed.

 

[Trebor76]

Are you saying I have to copy/paste 10 times into each of the sheets?

Yes.

Or is there a simpler way?

If you put the following in a standard module (like you had done originally)...

Option Explicit
Function SetFlag(strSheet As String, strCell As String, blnReturnDate As Boolean) As String

    On Error Resume Next
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    If blnReturnDate = True Then
        Sheets(strSheet).Range(strCell).Offset(0, 1).Value = Format(Now(), "YYYY/MM/DD")
    Else
        Sheets(strSheet).Range(strCell).Offset(0, 1).ClearContents
    End If
        
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Function

...and this event macro on each of the tabs:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then
        If StrConv(Target.Value, vbUpperCase) = "USED" Then
            Call SetFlag(Target.Worksheet.Name, Target.Address, True)
        ElseIf Len(Target.Value) = 0 Then
            Call SetFlag(Target.Worksheet.Name, Target.Address, False)
        End If
    End If

End Sub

you will only have to modify the function if your needs change (note I've incorporated clearing the date [or whatever is in] Col. C as well).

Thanks,

Robert

 

[RoryA]

I tried RoryA's suggestion of inserting a module and that did not work.

That is not what I said. I said it needed to be in the code module for the worksheet in question.

 

[Burrgogi]

@Trebor76: I appreciate your modified code and all of your help in this personal endeavor or mine. I'm all set now.

@RoryA: I have to say I was a very tired due to an exhaustive yard work project when I read your posting. It's possible I misread or misunderstood your comment. Apologies.

 

[Trebor76]

I appreciate your modified code and all of your help in this personal endeavor or mine. I'm all set now.

Thanks for letting us know an you're welcome