Same macro running from earlier opened workbook

[Guest]

I have a macro (in template) that automatically launches when the user starts
a new workbook with the template. I create (via this template), save and
close the first workbook. If I start a new workbook with this template,
Excel open the first workbook then proceeds to use the macros in it as
opposed to the ones in the newest workbook. Why? What can I do to stop this
behavior?

 

[Dave Peterson]

Whatever you have that invokes those macros (shape or button or what???) still
is tied back to the original workbook template.

You could have a macro run that reassigns the macro to the objects in the
Auto_Open procedure (or workbook_open event).

Kind of like:

Option Explicit
Sub auto_open()
With ThisWorkbook.Worksheets("Sheet1")
.Buttons("button 1").OnAction = "'" & .Parent.Name & "'!RunMacro1"
.Shapes("rectangle 1").OnAction = "'" & .Parent.Name & "'!RunMacro2"
End With
End Sub

If you're using a button from the Forms toolbar on a worksheet, you may want to
replace them with commandbuttons from the Control toolbox toolbar. The macros
aren't assigned to those type buttons, so the problem won't occur.

If you're creating a toolbar, I think you'll find it easier to create the
toolbar when the workbook opens--and delete the toolbar when you close the
workbook.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Guest]

Thanks for the reply Dave.

"ThisWorkbook" window auto-executes as follows:

Private Sub Workbook_Open()
ImportMultiLevelBom_PS
End Sub

ImportMultiLevelBom_PS is a Sub in a standard module.
ImportMultiLevelBom_PS really only needs to execute once (to populate the a
sheet with data) but I'm executing it everytime the workbook opens.
ImportMultiLevelBom_PS does have an If expression which checks a cell to
determine if the sheet has been populated or not. I guessing there's a
better way to auto-excute code only once, I just don't know how.

In any case, I need to somehow restructure this so I don't execute code from
the previous wookbook.

 

[Dave Peterson]

When you create a new workbook based on this template, you're saying that
ImportMultiLevelBom_PS will run from the .xlt file--not from the workbook that
was just created?

I couldn't reproduce that. It always ran from the newly created workbook.

I put
msgbox thisworkbook.fullname
at the top of that ImportMultiLevelBom_PS procedure to make sure.

And your solution seems ok with me. You could do the check in your
workbook_open event:

Private Sub Workbook_Open()
if me.worksheets("somesheetnamehere").range("a1").value = "" then
ImportMultiLevelBom_PS
end if
End Sub

 

[Guest]

I'm sorry, Workbook_Open is not the problem, it is my right click menu. I
have added your OnAction code to my menu but it still opens the previous
(closed) workbook to execute the macro from there. The only way to make it
stop doing this is to exit Excel, launch Excel, create the new workbook from
the template.


Private Sub xlApp_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As
Range, _
Cancel As Boolean)

'Add or remove Hyperlink menu items based on whether or not Hyperlink(s)
are selected

Dim oCtrl As CommandBarControl
Dim oBtn As CommandBarControl
Dim bModelsMenusExist As Boolean
Dim bModelsRefShExists As Boolean
Dim bHLMenusExist As Boolean
Dim bHyperLinksInRange As Boolean
Dim oWorksheet As Worksheet

Set oWorksheet = ThisWorkbook.Worksheets.item("References")
bModelsRefShExists = Not oWorksheet Is Nothing
'Debug.Print "bModelsRefShExists = " & bModelsRefShExists

For Each oCtrl In Application.CommandBars("Cell").Controls
If oCtrl.Caption = "Copy Hyperlinked Docs To..." Then
bHLMenusExist = True
ElseIf oCtrl.Caption = "Model Documents Exist (PDM/Master/Design
Library)?" Then
bModelsMenusExist = True
End If
Next
'Debug.Print "bModelsMenusExist = " & bModelsMenusExist

'
If bModelsRefShExists And Not bModelsMenusExist Then
Set oCtrl =
Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, _
Temporary:=True)
oCtrl.Caption = "Model Documents Exist (PDM/Master/Design Library)?"
oCtrl.OnAction = "ModelDocsExist"
ElseIf Not bModelsRefShExists Then
For Each oCtrl In Application.CommandBars("Cell").Controls
If oCtrl.Caption = "Model Documents Exist (PDM/Master/Design
Library)?" Then
oCtrl.Delete
End If
Next
End If

'Hyperlink related menu items
bHyperLinksInRange = Target.Hyperlinks.Count > 0
If bHyperLinksInRange And Not bHLMenusExist Then
Set oCtrl =
Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, _
Temporary:=True)
oCtrl.Caption = "Copy Hyperlinked Docs To..."
oCtrl.OnAction = "'" & Sh.Parent.Name & "'!CopyHyperlinkedDocsTo"

Set oCtrl =
Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, _
Temporary:=True)
oCtrl.Caption = "Zip && Email Hyperlinked Docs..."
oCtrl.OnAction = "'" & Sh.Parent.Name & "'!ZipAndEmailHyperlinkedDocs"

' Set oCtrl =
Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, _
' Temporary:=True)
' oCtrl.Caption = "Print Hyperlinked Docs..."
' oCtrl.OnAction = "PrintHyperlinkedDocs"
ElseIf Not bHyperLinksInRange Then
For Each oCtrl In Application.CommandBars("Cell").Controls
If oCtrl.Caption = "Copy Hyperlinked Docs To..." Then
oCtrl.Delete
ElseIf oCtrl.Caption = "Zip && Email Hyperlinked Docs..." Then
oCtrl.Delete
End If
Next
End If
End Sub

 

[Dave Peterson]

You could close that template (I bet you have a routing that sets xlApp to
nothing that'll stop these application events).

Then you could run the program that instantiates the application event using the
workbook_open event in this new workbook.

This works ok for me when I'm testing stuff. I just run the
workbook_beforeclose event and rerun the workbook_open event to turn things back
on.

I'm sorry, Workbook_Open is not the problem, it is my right click menu. I
have added your OnAction code to my menu but it still opens the previous
(closed) workbook to execute the macro from there. The only way to make it
stop doing this is to exit Excel, launch Excel, create the new workbook from
the template.

Private Sub xlApp_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As
Range, _
Cancel As Boolean)

'Add or remove Hyperlink menu items based on whether or not Hyperlink(s)
are selected

Dim oCtrl As CommandBarControl
Dim oBtn As CommandBarControl
Dim bModelsMenusExist As Boolean
Dim bModelsRefShExists As Boolean
Dim bHLMenusExist As Boolean
Dim bHyperLinksInRange As Boolean
Dim oWorksheet As Worksheet

Set oWorksheet = ThisWorkbook.Worksheets.item("References")
bModelsRefShExists = Not oWorksheet Is Nothing
'Debug.Print "bModelsRefShExists = " & bModelsRefShExists

For Each oCtrl In Application.CommandBars("Cell").Controls
If oCtrl.Caption = "Copy Hyperlinked Docs To..." Then
bHLMenusExist = True
ElseIf oCtrl.Caption = "Model Documents Exist (PDM/Master/Design
Library)?" Then
bModelsMenusExist = True
End If
Next
'Debug.Print "bModelsMenusExist = " & bModelsMenusExist

'
If bModelsRefShExists And Not bModelsMenusExist Then
Set oCtrl =
Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, _
Temporary:=True)
oCtrl.Caption = "Model Documents Exist (PDM/Master/Design Library)?"
oCtrl.OnAction = "ModelDocsExist"
ElseIf Not bModelsRefShExists Then
For Each oCtrl In Application.CommandBars("Cell").Controls
If oCtrl.Caption = "Model Documents Exist (PDM/Master/Design
Library)?" Then
oCtrl.Delete
End If
Next
End If

'Hyperlink related menu items
bHyperLinksInRange = Target.Hyperlinks.Count > 0
If bHyperLinksInRange And Not bHLMenusExist Then
Set oCtrl =
Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, _
Temporary:=True)
oCtrl.Caption = "Copy Hyperlinked Docs To..."
oCtrl.OnAction = "'" & Sh.Parent.Name & "'!CopyHyperlinkedDocsTo"

Set oCtrl =
Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, _
Temporary:=True)
oCtrl.Caption = "Zip && Email Hyperlinked Docs..."
oCtrl.OnAction = "'" & Sh.Parent.Name & "'!ZipAndEmailHyperlinkedDocs"

' Set oCtrl =
Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, _
' Temporary:=True)
' oCtrl.Caption = "Print Hyperlinked Docs..."
' oCtrl.OnAction = "PrintHyperlinkedDocs"
ElseIf Not bHyperLinksInRange Then
For Each oCtrl In Application.CommandBars("Cell").Controls
If oCtrl.Caption = "Copy Hyperlinked Docs To..." Then
oCtrl.Delete
ElseIf oCtrl.Caption = "Zip && Email Hyperlinked Docs..." Then
oCtrl.Delete
End If
Next
End If
End Sub

 

[Dave Peterson]

One more thing, if you're really want to use application events--that affect all
the open workbooks, you may want to consider moving all that code into an
addin. And tell the users to load that addin.

If you only wanted the worksheets in that workbook affected, why not just use
workbook level events.

If you only wanted a worksheet (or a few), how about using the worksheet event?

 

[Guest]

That was it! I have to kill xlapp upon close.

Thank you Dave!

 

[Guest]

Great points. Will do.

One more thing, if you're really want to use application events--that affect all
the open workbooks, you may want to consider moving all that code into an
addin. And tell the users to load that addin.

If you only wanted the worksheets in that workbook affected, why not just use
workbook level events.

If you only wanted a worksheet (or a few), how about using the worksheet event?