Custom Toolbar problem

[Skip Bisconer]

I have created a custom toolbar that runs a macro to open
a user form and the toolbar open and closes with the
workbook. The macro resides in a module in the workbook.

I back up all my important files to a zip drive before
closing each time. If I open the workbook from the last
place I saved, ie. the hard drive, the toolbar opens the
form as it should. If I open from the other source, ie.
the zip drive and click the toolbar, it tries to reload
the workbook from the source I last saved, and gives me
the halt that the workbook is already open.

I have tried renaming the file its trying to load. Doesn't
change the halt. If I save the workbook that works
properly, in the reverse order cited above, the problem
reverses. I have to remember where I last save the
workbook that worked properly. This happens on other
workbooks that I have custom toolbars that open and close
with a workbook.

My question is this. Should this be happening? If not what
can I change to stop it? My code is the following:

Private Sub Workbook_Open()
OpenMainForm
End Sub

Sub OpenMainForm()
'show the main form custom toolbar

Application.CommandBars("IrrigationMainForm").Visible =
True
End Sub

Thanks for any help you can give me.

Skip

 

[Greg Koppel]

Hi Skip,

You don't mention if you have attached the toolbar to the workbook, but you
should if you haven't. Then assign the macros when the workbook is opened,
and unassign when closed. The line below to delete the toolbar only deletes
it from the local list of toolbars, not the attached toolbar in the
workbook.

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

 

[Guest]

-----Original Message-----

Thanks for responding Greg.

I took your code and put it my module subing my toolbar
name. I only have one button so I ignored the toolbar
button action code. But it doesn't work for me. I do
notice if I am on a blank workbook and I right click the
toolbar and select my custom toolbar it will open the
workbook that functions properly. Do I have to put the
code in my Personal Workbook?

Skip

 

[Greg Koppel]

Hi Skip,

The code belongs in the workbok with the macros and toolbar. I am pretty
sure you still need to use the action code, though deleting the toolbar may
be sufficient. The custom toolbar shouldn't appear in the list of available
toolbars unless the workbook is open.

HTH, Greg

 

[Guest]

The toolbar option is available with Excel in general.
This is probably the problem. I better review how to set
up a custom toolbar workbook specific. Thanks for working
with me.

Skip