PERSONAL.XLS from Excel97 to Excel2000

[Eugene]

Hi All,

I've having difficulty trying to make an Excel2000 VBA macro work in
Excel97 - I've built and tested in 2000 but when implemented on 97,
the macro works very differently. Unfortunately my users only have
access to Excel97 (on NT4.0 OS!), so the first alternative for me is
to fix the problem within Excel97.

The macro is a user form which opens on opening a setup.xls file
(user opens this file to set it up). The form is essentially a big
install button which (on click) will insert a custom button and change
the caption of the button.

From there clicking on the new button will ask to assign a macro to it
which is also contained in the setup.xls file (not within macro, can't
seem to assign a macro via VBA). This is also where it gets tricky.

After assigning the macro to the button, on 2000, I guess that the
macro is automatically saved to the PERSONAL.XLS file so that
subsequent use of the new button calls the assigned macro. However on
97, subsequent pushes seem to try to reopen the setup.xls file in
order to access the macro.

My questions to this learned group are (finally):
1) Is my guess correct in that Excel 97 must have the xls file with
the macro in it open in order to access an assigned macro? Thus
running a macro found in a closed file will cause 97 to open that file
in order to run it?
2) Is there something I can do within VBA to assign the macro to the
button AND have it running the macro without opening the setup.xls
file? This is an important factor, as the macro that runs from the
button MUST have a specific number of windows open (I realise this can
be changed, but so far it remains easier as a training issue to keep
this requirement)

Thanks in Advance
Eugene

 

[Dave Peterson]

#1. I created a book7.xls with a single macro in it:
Option Explicit
Sub testme99()
MsgBox "hi from " & ThisWorkbook.Name
End Sub


Then I had another workbook that had this macro in it:

Option Explicit
Sub testme01()
Dim myBTN As Button
With ActiveSheet
With .Range("a1")
Set myBTN = .Parent.Buttons.Add(Left:=.Left, Width:=.Width, _
Height:=.Height, Top:=.Top)
End With
myBTN.OnAction = "'C:\my documents\excel\book7.xls'!testme99"
End With
End Sub

When I clicked on that new button in A1, I got an "enable macros" prompt. If I
said, ok, then I got the msgbox back.

#2. I could use a macro in a closed book, but to actually run the code, that
workbook has to be opened.

Maybe you could count the workbooks and include the code workbook in your total.