Help locking a macro after its been run

[Buns1976]

Hi Everyone,

Not really sure how to do this but I have a macro assigned to a button in a worksheet. What I would like to do is AFTER
the macro has been run, lock it until Workbook has been closed and reopened. If the button is clicked prior to closing and reopening
display a message that would say "Clear Form Has Already Run".

Thank you!!

Sub RUN_ALL_MACROS()'
' RUN_ALL_MACROS Macro
'
    Application.ScreenUpdating = False
    Application.Run "LOTTERY.xlsb!UNPROTECT"
    Application.Run "LOTTERY.xlsb!CLEAR_CHECKBOXS"
    Application.Run "LOTTERY.xlsb!RESTORE_COLUMN_K"
    Application.Run "LOTTERY.xlsb!COPY_TO_BACKUP"
    Sheets("LOTTERY").Select
    Range("B2").Select
    Application.CutCopyMode = False
    Application.Run "LOTTERY.xlsb!LOTTERY_SHEET_CLEAR"
    Application.CutCopyMode = False
    Application.Run "LOTTERY.xlsb!Copy_Prev_Scan"
    Application.Run "LOTTERY.xlsb!PROTECT"
    Application.ScreenUpdating = True
    
End Sub

 

[Buns1976]

Hi Mumps,

Wondering how to edit this macro so it only protects E2:E71? In its current state it protects all cells in the sheet and we don't
want to protect B2:C2. Its actually a little weird because B2 is a DATE entry and as soon as I enter the date, all cells are then locked.

Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)ActiveSheet.UNPROTECT Password:="1875"
Target.Locked = True
ActiveSheet.PROTECT Password:="1875"


End Sub

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E2:E71")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="1875"
    Target.Locked = True
    ActiveSheet.Protect Password:="1875"
End Sub

 

[Buns1976]

Hi Mumps,

So I can enter the date in B2 and Select a name from the dropdown in C2 as long as I do that FIRST. As soon as I populate E2, B2:C2 are locked again?

Thanks

 

[mumps]

The target range in the macro is "E2:E71" so only that range should be affected. Perhaps other macros you are using are causing the problem. Are you using the macro as it is written or are you adding it to already existing code?

 

[Buns1976]

Hi Mumps,

I figured it out. Running the previous code was protecting ALL cells so it had changed the properties on B2:C2 to locked.
Once I changed them to unlocked it appears to be working fine.

AS ALWAYS, THANK YOU!!

 

[mumps]

My pleasure.