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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

mumps

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

ADVERTISEMENT

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
10,238

ADVERTISEMENT

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
10,238
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,127,033
Messages
5,622,314
Members
415,891
Latest member
Oksana88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top