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]

You could add a line of code at the end of the macro that makes the button invisible. Then create a Workbook_Open macro that makes the button visible when the workbook is opened.

 

[EddieL]

Hi Buns

You could declare a global variable and set it to false on workbook_open. Check its value at the start of the macro and if false run the macro and set the value to true at the end of the macro. You could add the message at the checking value stage and exit sub after message shown.

Hope this helps

EddieL

 

[Buns1976]

Hi Mumps!

So I manged to get it hid after click however I'm having a time getting it to unhide on workbook open.
"Runtime error 424. Object Required".Any ideas?


Macro is located in THIS WORBOOK

Private Sub Workbook_Open()



ClearForm.Enabled = True




End Sub

 

[mumps]

Can you post the code you used to hide it?

 

[Buns1976]

Here you go!

Private Sub ClearForm_Click()ClearForm.Enabled = False
End Sub

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Buns1976]

Hi Mumps,
This link should get you to where you want to go.
This is just a simple workbook with a command button that hides the button after click and
then there is a macro to unhide it on open which I can't get to work.

Thanks for your help!



https://www.dropbox.com/sh/97xr6lvidpvyq9w/AAAQkVA0O1rqYy0mN8hLM6eRa?dl=0

 

[mumps]

Click here to download you file. Each time you click the button, it will disappear. The next time you open the file, the button will re-appear. The macro will prompt the user to confirm if he/she wants to clear the form and if the answer is "Yes" then the form will clear and the button will disappear. I thought this might be a good idea just in case someone clicks the button by accident.

 

[Buns1976]

Thanks Mumps,

Thank you! I'm having an issue getting the "ClearForm.Visible = False" to work correctly where it was intended. If you look at the code in the OP below, it needs to fit in on 3rd line from the bottom
before "PROTECT". That button runs that entire Macro which is several wrapped up in one!

Thanks

[COLOR=#333333]Sub RUN_ALL_MACROS()'[/COLOR]' 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
     [COLOR=#333333]End Sub[/COLOR]