cancel execution of a macro with a macro

ramasterre

Active Member
Joined
Oct 5, 2004
Messages
253
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?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
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 :LOL:
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Ciao chiello. :biggrin:

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

Code:
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
 

Forum statistics

Threads
1,147,686
Messages
5,742,623
Members
423,744
Latest member
bkirtland

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top