Verify before proceeding

[snowing]

Hello,

Is there a way that I can check to see if a macro was run from within that macro before it proceeds with a part of the macro, maybe have it check something before proceeding with the rest of it ?
What I want it to do is stop someone from run a macro again if it was already ran, and if it wasn't ran before to check some column headings to make sure they are all there, before proceeding with the macro.

Thanks

[tony h]

There are various ways of doing this but I think the easiest way is to use a cell on a worksheet (maybe a hidden sheet) to store an update/updated flag.

Then use the workbook.open event to set the flag to "update".

Before running the checking macro see if the is "update"
Then when you run the macro which does the update reset the flag to "updated".

Note that if you try and hold the flag as a variable it will get reset each time the code finishes.

regards

[Ardus Petus]

Dim bMacro1WasRun as boolean

sub MySub()
if bMacro1WasRun then
<do something>
else
<do whatever>
end if
end sub

sub Macro1
<do some stuff>
bMacro1WasRun = true
end sub

Et voilà

--
AP

"snowing" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Hello,
>
> Is there a way that I can check to see if a macro was run from within
> that macro before it proceeds with a part of the macro, maybe have it
> check something before proceeding with the rest of it ?
> What I want it to do is stop someone from run a macro again if it was
> already ran, and if it wasn't ran before to check some column headings
> to make sure they are all there, before proceeding with the macro.
>
> Thanks
>
>
> --
> snowing
> ------------------------------------------------------------------------
> snowing's Profile:
> http://www.excelforum.com/member.php...o&userid=34576
> View this thread: http://www.excelforum.com/showthread...hreadid=547810
>

[snowing]

Thanks for the help.

Ardus can you explain your code a little for me, I would need something to work from within the one macro

[Ardus Petus]

Tony's solution is smarter because it saves the "MacroWasRun" status

You place a FALSE/TRUE value in some definite cell, then you test its
contents.

HTH
--
AP


"snowing" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Hello,
>
> Is there a way that I can check to see if a macro was run from within
> that macro before it proceeds with a part of the macro, maybe have it
> check something before proceeding with the rest of it ?
> What I want it to do is stop someone from run a macro again if it was
> already ran, and if it wasn't ran before to check some column headings
> to make sure they are all there, before proceeding with the macro.
>
> Thanks
>
>
> --
> snowing
> ------------------------------------------------------------------------
> snowing's Profile:
> http://www.excelforum.com/member.php...o&userid=34576
> View this thread: http://www.excelforum.com/showthread...hreadid=547810
>

[tony h]

Here is some code that does not use a cell for storage but one of the document properties - if necessary you can create a custom property. The advantage of using one of these properties is that it does not inflict anything on the sheets themselves.

Private Sub Workbook_Open()
ThisWorkbook.BuiltinDocumentProperties("Comments") = "N"
End Sub



Sub mySub()
If ThisWorkbook.BuiltinDocumentProperties("Comments") = "N" Then
do my run once code
then set
ThisWorkbook.BuiltinDocumentProperties("Comments") = ""
End If

do my always code

End Sub

Cheers (and thanks Ardus)

[snowing]

Thanks guy for your help.

Tony, your code seem to skip the "run once code" and go right to "do my always code"

Am I doing something wrong?

*****************************************
Private Sub Workbook_Open()
ThisWorkbook.BuiltinDocumentProperties("Comments") = "N"
End Sub



Sub mySub()
If ThisWorkbook.BuiltinDocumentProperties("Comments") = "N" Then
do my run once code
then set
ThisWorkbook.BuiltinDocumentProperties("Comments") = ""
End If

do my always code

End Sub

[tony h]

The workbook_open module needs to be ThisWorkbook module (in VBA explorer VBAProject...Microsoft Excel Objects... ThisWorkbook)

The rest of the code in a standard module. Put some msgbox call in to track what happens eg

Private Sub Workbook_Open()
ThisWorkbook.BuiltinDocumentProperties("Comments") = "N"
MsgBox "initialise"
End Sub



Sub mySub()
If ThisWorkbook.BuiltinDocumentProperties("Comments") = "N" Then
MsgBox "run once"
ThisWorkbook.BuiltinDocumentProperties("Comments") = ""
End If

MsgBox "do always"

End Sub

you should get "initialise" when you open the workbook. The other two should appear as you run mySub

[snowing]

Thanks Tony, works great.