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

 

[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à

 

[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

 

[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 (e-mail address removed)...

 

[tony h]

Here is some code that does not use a cell for storage but one of th
document properties - if necessary you can create a custom property
The advantage of using one of these properties is that it does no
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 m
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 Su

 

[tony h]

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

The rest of the code in a standard module. Put some msgbox call in t
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 tw
should appear as you run mySu

 

[snowing]

Thanks Tony, works great.