Running a macro only once

[ted.jennings]

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

 

[Domski]

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

 

[Joe Was]

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

 

[onlyadrafter]

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

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

 

[ted.jennings]

I've used the soft code...thanks

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