View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default Same macro running from earlier opened workbook

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)

MAB wrote:

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