cancel execution of a macro with a macro

[ramasterre]

Hello

I have a macro (to log who goes in and out of the workbook) which runs whenever my workbook is opened. I need it to run ever time the workbook is opened except when it is first 'created'. The workbook is created via macros in another work book which find the last xls document created, opens its, clears all the values, and then downloads new values. My problem is I am trying to find a way to make the login macro not run if the macro that clears out the sheets is run. I have another macro that I want to run instead. Every time i "create" a new document the login macro pops up before the create macro can even run. Is there a way to put a line of code into the create macro to suppress the login macro?

 

[chiello]

Ciao ramasterre,
I have this idea:
when you want to "create" a new document, realize first a particular condition, i.e. a pseudo-password, which inhibits the execution of the login macro.
I try to explain better:

Code of the macro which creates the new document (I suppose file name is Cartel1.xls):

<font face=Courier New><SPAN style="color:#007F00">'...</SPAN>
<SPAN style="color:#007F00">'Before the WorkbookOpen instruction</SPAN>
Range("A1") = "pseudo-password"
Workbooks.Open Filename:="YourDirectory\YourFile.xls"
<SPAN style="color:#007F00">'...</SPAN></FONT>


Code in Log in macro (I suppose file name is YourFile.xls)

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">Dim</SPAN> StrPseudoPswrd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    StrPseudoPswrd = ""
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    StrPseudoPswrd = Workbooks("Cartel1.xls").ActiveSheet.Range("A1")
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
    <SPAN style="color:#00007F">If</SPAN> StrPseudoPswrd = "pseudo-password" <SPAN style="color:#00007F">Then</SPAN>
        Workbooks("Cartel1.xls").ActiveSheet.Range("A1").ClearContents
        <SPAN style="color:#007F00">'Run Other MACRO</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        <SPAN style="color:#007F00">'Run Login MACRO</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Is it helpfull

Post for feedback

 

[parry]

Ciao chiello.

An interesting problem ramasterre. Heres another approach. You can write code that deletes code, so you can call a procedure which deletes a line in the Workbook_Open event, thus creating a workbook_open event that will run from the second time onwards.

This code is based on code from here http://www.cpearson.com/excel/vbe.htm

Private Sub Workbook_Open()
Call DeleteProcedure
MsgBox "Hello"
End Sub

Sub DeleteProcedure()

Dim VBCodeMod
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
With VBCodeMod
    StartLine = .ProcBodyLine("Workbook_Open", vbext_pk_Proc) + 1 'the second line in the procedure
    HowManyLines = 1
    .DeleteLines StartLine, HowManyLines
End With

End 'Dont run the rest of the Workbook_Open procedure

End Sub