Help locking a macro after its been run

Buns1976

Board Regular
Joined
Feb 11, 2019
Messages
138
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!!

Code:
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,744
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

New Member
Joined
Jan 20, 2019
Messages
5
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

Board Regular
Joined
Feb 11, 2019
Messages
138
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

Code:
Private Sub Workbook_Open()



ClearForm.Enabled = True




End Sub
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,744
Can you post the code you used to hide it?
 

Buns1976

Board Regular
Joined
Feb 11, 2019
Messages
138
Here you go!

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

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,744
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.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,744
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

Board Regular
Joined
Feb 11, 2019
Messages
138
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





Code:
[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]
 

Forum statistics

Threads
1,089,201
Messages
5,406,803
Members
403,106
Latest member
AliO

This Week's Hot Topics

Top