macro in 2003 template asks for non-existent XLS

[Dave D]

Hi all:

Created a template in 2003 with a medium sized set of
macros. Call it "work.xlt".

Saved it, doubleclicked to open new "work1.xls". Noticed
a couple of problems, closed work1.xls without saving.

Opened work.xlt, made my changes, started to test, and
now all macros seem to want to open "work1.xls".
Simplest are some "goto sheet" macros, just select sheet
then select cell. When you run any of these macros, a
message box appears that "work1.xls cannot be found".

even tried saving under another name as play.xlt
template, but macros still ask for work1.xls.

Any suggestions? Thanks for your help

 

[Greg Koppel]

Hi Dave,

I assume you are using a toolbar to run the macros. If so, you should
assign/unassign the macros to the buttons when the workbook is opened.

Sub Auto_open()
Toolbars("LogForm").Visible = True
Toolbars("LogForm").ToolbarButtons(1).OnAction = "PhoneLogger"
Toolbars("LogForm").ToolbarButtons(2).OnAction = "LateLogger"
End Sub
Sub Auto_close()
On Error Resume Next
Toolbars("LogForm").ToolbarButtons(1).OnAction = ""
Toolbars("LogForm").ToolbarButtons(2).OnAction = ""
Toolbars("LogForm").Visible = False

Toolbars("LogForm").Delete
On Error GoTo 0
End Sub

HTH, Greg

 

[Frank Kabel]

Hi
could you post one of these macros?
Probably a fixed workbook reference

 

[Guest]

Thanks, both. I'll try the assign, though I'm using a
menu list, not buttons. Hopefully, it's similar.

The macros do not have obvious workbook references. The
simplest, (which still does not work anymore), has only
two commands, e.g.,

sub gotosht1 ()
sheet("sheet1").select
range("C3").select
endsub

What I'm still puzzled by is why it's asking
for "work1.xls" when I'm working in "templ2.XLT" (which,
recall, was renamed from "work.xlt").

I also saw a reference to hidden names in the knowledge
base, with a routine to display and delete any unwanted
ones. I'm going to try that also if the assign approach
does not work.

Thanks for your help so far.

Dave

 

[Dave D]

Hi Greg,

Thanks for the input. It did the trick! I'm using menus,
though, so solution was slightly different... Locate menu
on active menubar, then loop through controls to assign
macros.

When I read your message, I finally looked closely at the
macro assignment box in Tools/Customize for my menu items,
and saw that they now contained a reference to that
missing spreadsheet as the source location for the macro.
I'm still puzzled as to how that happened, i.e., why the
XLT was updated when I was working in the generated XLS.
But, regardless, your solution works beautifully! Thanks
again.

Dave D