Help locking a macro after its been run

Buns1976

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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,718
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
149
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
11,718
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Can you post the code you used to hide it?
 

Buns1976

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

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

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,718
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows

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
11,718
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
149
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]
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,644
Messages
5,832,857
Members
430,173
Latest member
citullipan

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