How to make macro run only once

[Hiport]

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.

 

[KSTWS]

You can disable the button after pressing the button.
With the Workbook_Open event you make sure the button is enabled.

 

[Hiport]

You can disable the button after pressing the button.
With the Workbook_Open event you make sure the button is enabled.

is this correct?

Application.EnableEvents = False
Workbooks.Open Filename:="WorkbookPFullPath"
Application.EnableEvents = TrueApplication.EnableEvents = False

 

[KSTWS]

Try:
Me.CommandButton1.Enabled = False

 

[KSTWS]

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

Private Sub CommandButton1_Click()
Me.CommandButton1.Enabled = False
End Sub

 

[Hiport]

Try:
Me.CommandButton1.Enabled = False

ok i am doing a test and i get compile errror invalid use of me keyword

Sub test()
Me.CommandButton1.Enabled = False
Range("A1").Value = "Test"
 
End Sub

 

[KSTWS]

Sheets("YourSheetName").CommandButton1.Enabled = False

 

[Peter_SSs]

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>

 

[Hiport]

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'

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

 

[KSTWS]

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