Help locking a macro after its been run

Buns1976

Board Regular
Joined
Feb 11, 2019
Messages
194
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Can you post the code you used to hide it?
 
Upvote 0
Here you go!

Code:
Private Sub ClearForm_Click()ClearForm.Enabled = False
End Sub
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,218,560
Messages
6,143,200
Members
450,469
Latest member
brent3162

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
Back
Top