How to make macro run only once

H

Hiport

Active Member
Joined
May 9, 2008
Messages
455
Hi, is it possible to put a message box in a VBA code so that the macro only runs once, else the worbook needs to reopened again for it to work. Is this the best way from preventing the macro to run again?

If i have multiple users, there is always a chance one might accidently press the command button twice.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
You can disable the button after pressing the button.
With the Workbook_Open event you make sure the button is enabled.
 
Upvote 0
KSTWS said:
You can disable the button after pressing the button.
With the Workbook_Open event you make sure the button is enabled.
Click to expand...


is this correct?

Code: 
Application.EnableEvents = False
Workbooks.Open Filename:="WorkbookPFullPath"
Application.EnableEvents = TrueApplication.EnableEvents = False
 
Upvote 0
Private Sub Workbook_Open()
Sheets("Sheet1").CommandButton1.Enabled = True
End Sub

Private Sub CommandButton1_Click()
Me.CommandButton1.Enabled = False
End Sub
 
Upvote 0
Try this (assumes the CommandButton is from the Control Toolbox toolbar not the Forms toolbar)

1. Your button click code:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#007F00">' rest of code here</SPAN><br>    CommandButton1.Enabled = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

2. Code for ThisWorkbook module (adjust sheet name and command button name if required)

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br>    ActiveWorkbook.Sheets("Button").OLEObjects("CommandButton1").Object.Enabled = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
Peter i get subscript out of range for the second part of the code, and also when i press the command button i get a message saying cannot run the macro "Diable command button.xlsm!test'


Peter_SSs said:
Try this (assumes the CommandButton is from the Control Toolbox toolbar not the Forms toolbar)

1. Your button click code:

Private Sub CommandButton1_Click()
' rest of code here
CommandButton1.Enabled = False
End Sub


2. Code for ThisWorkbook module (adjust sheet name and command button name if required)

Private Sub Workbook_Open()
ActiveWorkbook.Sheets("Button").OLEObjects("CommandButton1").Object.Enabled = True
End Sub
Click to expand...
 
Upvote 0
Did you try as mentioned in post #5?

Private Sub Workbook_Open()
Sheets("YourSheetName").CommandButton1.Enabled = True
End Sub

Private Sub CommandButton1_Click()
Sheets("YourSheetName").CommandButton1.Enabled = False
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Saving dialog box hanging after running macro
2
Replies
16
Views
375
Alex Blakenburg
A
K
  • Solved
Undo Stack gets cleared running VBA - Possible Solution to fix this?
Replies
5
Views
119
kellman
K
E
  • Solved
Run code after set period of time
2
Replies
14
Views
325
EssKayKay
E
L
  • Question Question
Getting data to go to the correct columns
Replies
5
Views
291
mumps
mumps
P
  • Question Question
Excel/365 VBA crash issues recently
Replies
0
Views
507
picklefactory
P

Forum statistics

Threads
1,224,534
Messages
6,179,390
Members
452,909
Latest member
VickiS

Share this page

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