Running a macro only once

T

ted.jennings

New Member
Joined
Feb 4, 2009
Messages
2
I have macro that is called up from a button on a sheet.
For various reasons this button is supposed to be clicked after other things are done first, and then not ever used again (unless the process is restarted)

I would like to include some code to maybe pop up a msgbox if the button is clicked more than once asking if the user is sure they want to run the macro again.

I know I can hide/delete the button after the macro runs once, but I'd rather not do it that way.

Thanks
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Hi,

When I've done this I've used a table on a veryhidden worksheet that keeps track of what macros have been run by updating cells on the sheet. You then just have a bit of code at the end of each macro to update the table to say the macro has been run and a check at the start of each one to see whether it already has.

Dom
 
Upvote 0
Soft switch the code so it tests to see if it is safe to run. The first time it is run it is OK the second time it is not. The switch re-sets itself each time the workbook is opened, but will only run the code once!

Public mySW1 As Boolean

Sub myButton()

If mySW1 = True Then Exit Sub

'Run your code!

mySW1 = True
End Sub
 
Upvote 0
Hello,

as part of the code, can you add a value to a cell, e.g. Z1, then also check the value of the cell then ask, like this

Code: 
Sub CHECK_FOR_TIMES_RUN()
    If Range("Z1").Value = 1 Then
        MY_REPLY = MsgBox("Do you want to run again?", vbYesNo, "ALREADY RUN")
        If MY_REPLY = vbNo Then
            End
        End If
    End If
    Range("Z1").Value = 1
            'yourcode here
End Sub
 
Upvote 0
I've used the soft code...thanks


Code: 
Public mySW1 As Boolean
 
If mySW1 = True Then
        myresponse = MsgBox("You have run this already. Do you want to run it again?", 292, "Run Error!")
            If myresponse = vbNo Then
            End
            End If
        End If
    mySW1 = True
call sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Disable a Macro
Replies
3
Views
167
saboh12617
S
M
  • Question Question
Error 1004 - Application defined or object defined error.
Replies
4
Views
57
MeaclH
M
E
  • Solved
Run code after set period of time
2
Replies
14
Views
325
EssKayKay
E
T
  • Solved
Move cursor to a specific cell after data has been entered
Replies
4
Views
349
Peter_SSs
Peter_SSs
P
  • Question Question
Checking for Worksheets that already exist
2
Replies
11
Views
311
Logit
Logit

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