I developed an Excel workbook for budget management using Excel 2000. The
workbook contains a number of macros that execute from custom toolbars. The
toolbars are attached to the workbook. Appropriate toolbars are turned
on/off selectively with VB based on the particular worksheet that is active.
The program also retrieves current pricing information from the web.
I am trying to move this program to Excel 2007 (Microsoft Office 2007
Professional / Vista Home Premium). To start the process, I save the .xls
file from Excel 2000 to a USB thumb drive.
I then move the USB drive to the Vista machine, start Excel 2007, and open
the .xls file from the thumb drive. Macro security is set to notify but not
install macros. I accept both options (enable macros and enable retrieval of
external data). The Add-In tab adds to the ribbon and the correct custom
toolbar is visible on the add-in tab. The toolbars change as expected when
I move from one worksheet to another.
I have also copied the file from the thumb drive to a Trusted Source folder
and opened it from there. The results are the same.
But, clicking any button on a custom toolbar gives an immediate error -- a
message box titled €œMicrosoft Visual Basic€� which contains a red circle with
a white €œX€� and the number 400. There are 2 options in the message box, €œOK€�
or €œHelp€�. €œHelp€� goes to the introduction to the help system and €œOK€�
returns the user to the worksheet.
The macros are included with the file and execute correctly in Excel 2007.
If I open the list of macros, select a macro, and click run, the macro
executes as it should. Data can be retrieved from the web without problem.
Commenting out all of the commandbar commands (.enabled=, .visible=) that are
used to activate the appropriate command bar did not change the error.
Saving the file as a macro enabled Excel 2007 file (.xlsm) and opening that
file results in the same error.
----------------
I know all of the controls could be easily added to the UAT but there are a
several controls and I would like to keep a single version of the workbook
that will work on both machines.
One guess is that the path name of the macros assigned to the toolbar
buttons may not be correct. Occasionally in Excel 2000 the path and file
name for an assigned macro reverts to another version of the workbook. This
may be a problem with Excel 2000 and not with 2007.
Questions:
1. Is it possible to get the tool bars to work? Sooner or later I will
have to learn how to add commands to the ribbon, but I would rather not do
that at this time.
2. Is there any way in Excel 2007 to edit the path/name of the macro
assigned to a button in the custom toolbar? Is there any way to just look at
the path/name of the assigned macro?