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

 

[mumps]

Without seeing your actual file and the macros within it, it's hard to see why that line of code doesn't work. As you saw in the sample file I attached, it does work. I wouldn't think it matters where in the macro that line goes. Once the button is clicked, the button should disappear and the rest of the macro will then run.

 

[mumps]

In the code module for ThisWorkbook, try:

Private Sub Workbook_Open()
    Sheets("LOTTERY").CommandButton1.Visible = True
    MsgBox "RUN PLU REPORT BEFORE PROCEEDING"
End Sub

In the code module for the LOTTERY sheet, try:

Private Sub CommandButton1_Click()
' RUN_ALL_MACROS Macro
    Application.ScreenUpdating = False
    CommandButton1.Visible = False
    Application.Run "LOTTERY.xlsb!UNPROTECT"
    Application.Run "LOTTERY.xlsb!CLEAR_CHECKBOXS"
    Application.Run "LOTTERY.xlsb!RESTORE_COLUMN_L"
    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]

Mumps, I do believe that is the ticket so thank you very much. I apparently had "FormClear in the wrong order and should have combined the 2 on open!!

THANK YOU!!!!

 

[mumps]

My pleasure.

 

[Buns1976]

OK so hopefully the last piece of the puzzle on this workbook. I have a range of cells(E2:E71). Each cell is populated with a USB Scanner. What
I would like to do is once that scan is populated in a given cell in the range, protect it from being edited manually. I have read a couple of things
regarding putting a timer on the cell where you would have only a few seconds to edit which would be nearly impossible or possibly locking those
cells after population with the thought in mind that the rest of the worksheet is already protected except for this range. What would be the best way
to go about this?

Thanks

 

[mumps]

I think that what you want to do is beyond my range of experience. The only thing that I can think of is to see if the entry of the bar code will trigger a Worksheet_Change event. If it does, then that macro can lock the cells when triggered.

 

[Buns1976]

Mumps, Here is what I did on a test workbook and it works well. Now to get it into my group of macros.

Top macro protects after entry and bottom macro unprotects when reopening workbook.

Private Sub Worksheet_Change(ByVal Target As Range)ActiveSheet.Unprotect Password:="test"
Target.Locked = True
ActiveSheet.Protect Password:="test"


End Sub

Private Sub Workbook_Open()'
' Macro1 Macro
'


'
    ActiveSheet.Unprotect
    Range("B1:B26").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
End Sub

 

[mumps]

I'm glad it worked out. I didn't know if the entry of the data by your bar code reader would trigger the macro. That's why I suggested you try it. Apparently, some readers will work that way but others will not.

 

[Buns1976]

As long as you are able to disable the tab suffix on the scanner it works. In this situation we are tabbing down so it works!
Would probably work on a horizontal tab with tab suffix enabled but haven't tested it.

Thanks a million!!

 

[mumps]

Glad to help.