preventing macro rerun 1

[Luis939]

hey this is kinda important, is there anyway to run a macro, and somehow have it record that it has already been used for a specific file so that it wont be run again, say until the file is reopen, or anything similar to that, thanks!

 

[xlbo]

4335
Kinda - it's a bit of a kludge but at the end of the macro, just do something like enter "RUN" in a specific cell
At the start of the macro, just check to see if the text is there:

Sub Whatever()
if range("Check&quot.text = "RUN" then
msgbox "You cannot re-run this macro until the workbook is re-opened"
exit sub
else
end if

'code
'code
'code

range("Check&quot.value = "RUN"
end sub

Then in the workbook_open event, clear the text

Range("Check&quot.value = ""

HTH

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Luis939]

ohhh alrightttt that actually makes sense, even if it is simple thanks alot, i shoulda thought about that too, much appreciated!!

 

[Zathras]

Simply declare a public variable in the code module, then test it when you run your macro and set it when the macro finishes:
[blue]

Option Explicit
Public bOneTimeOnly As Boolean

Sub MyMacro()
  If bOneTimeOnly = True Then
    MsgBox "Can't run again."
  Else
    'do stuff
    bOneTimeOnly = True
  End If
End Sub

[/color]

 

[Luis939]

that's a pretty good idea too, thanks!

 

[xlbo]

Zathras - something I've never really got my head round - Public Variables.....how long do they last.
Lets say for example that the code is run and the variable is set - when does the variable get reset ???

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Zathras]

AFAIK, the only way to reset it is with code or a File/Open (whether currently open or not). Selecting the .wks from the MRU list (when it is already open) does not reset it.

 

[xlbo]

So does it get reset on file close ??
Or will it just stick around ad infinitum ?

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Zathras]

When the .xls file is closed, all the associated macros disappear along with it.

Hmmmm, are you thinking of putting the code in personal.xls or in an add-in? Maybe then it sticks around until you exit Excel. I haven't worked with either case. Might be worth an experiment or two.

 

[xlbo]

Kewl - might have to start working with public variables now - thanks for clearing that up for me - have a star
And yes - got a potential use for my personal.xls - being as that stays open until excel is closed, it could be a way of keeping public variables open even when the workbook they are being used for is closed

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde