How can I get into VBA for a custom toolbar - Excel 97

[Jody]

I need to make a modification to one of the buttons in a
custom toolbar that was created by the developer before
me. I cannot for the life of me seem to be able to
determine where the code for this toolbar sits. How do I
get into the code for this toolbar? Some of the other
toolbars I have appear in my VBA edit screen, but I don't
see any files that seem to be associated with the toolbars
I need to edit. I tried searching the registry for the
names that Excel refers to these by in the Toolbars,
Customize... screen but nothing came up there. Can you
help? Thanks, Jody

 

[Jim Rech]

They might be COM add-ins. Do you see anything under Tools, Add-ins in the
VBE? Do the toolbars go away if you unload them?

 

[Rob Bovey]

Hi Jody,

It sounds like this might be an attached toolbar. With the workbook in
question active, choose View/Toolbars/Customize from the Excel menu. Click
the Attach button on the Toolbars tab of the Customize dialog. If the
workbook has an attached toolbar, it will appear in the listbox on the
right-hand side of the Attach Toolbars dialog.

In order to modify an attached toolbar you first need to remove it from
the workbook by selecting it and clicking the Delete button (do not delete
the toolbar from the Customize dialog, only from the Attach Toolbars
interface). The toolbar will still be available in Excel. Make your
modifications to it and then reattach it to the workbook by going back into
the Attach Toolbars dialog, selecting the toolbar from the left-hand listbox
and clicking the Copy button to add it to the workbook. Click OK until your
out of all the dialogs and be sure to save the workbook or the changes won't
stick.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Jody]

Thanks to both of you for answering. There is definitely
not an add-in for the toolbar.

Also, as Rob mentioned, I checked in the Attach Toolbars
dialog and the name of the toolbar does appear on the left
hand side but it does not appear on the right hand side in
that screen.

The custom toolbar I am trying to modified is tied in with
some VBA code we use. This code is loaded by setting the
alternate start-up location. The systems administrator
said that's all he has to do in order to set up a new
computer, he does nothing specifically to load the toolbar.

Now, I've done the obvious like to go through the code
that the pull-down menus use and search for the name that
Excel shows as the name of the toolbar. It doesn't find
anything. If it isn't getting loaded here, I can't
imagine where it is. I am so frustrated with this!

Is there a command line statement I can use in the
immediate window in order to see the function associated
with a button? I think I've done all the obvious stuff
like search for the name through the code that IS showing
in the VBA editor and checking the Add-Ins.

 

[Jim Rech]

So there is a file loading from the Alternate Startup File Location that
creates the toolbar? Is it an add-in (XLA)? The only way you can see if it
has toolbars attached is by making it the active workbook in Excel and then
go into Attach Toolbars. If it is an add-in you cannot make it active. So
click on the add-in's ThisWorkbook node in the VBE's Projects window and
press F4 to open the Properties window. Change the IsAddin property to
False.

The toolbar may not be attached but instead create by code at startup.
Search the VB project for "Commandbars" or "Menubars" for this code.

 

[Rob Bovey]

Hi Jody,

Are you sure there isn't a hidden workbook being opened that might have
this toolbar attached to it? Check the alternate startup file location. Any
file that exists there will be opened automatically when Excel starts up.
Toolbars can't appear out of thin air, so it's either being constructed in
code or it's been prefabricated and attached to a workbook that's being
opened. If you want to know the name of the procedure that a toolbar button
is assigned to, choose View/Toolbars/Customize from the menu, then
right-click over the toolbar button and select Assign Macro. The name of the
Macro that appears in the Macro name box of the Assign Macros dialog is the
one being run when that button is clicked.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *