If Then VB

[Spencer]

I have a code that runs everytime I open Excel that creates a toolbar. It is
not assigned to a workbook but rather is in my personal.xls macro file in
XLSTART. I've been able to get it to work fine when I open up Excel, close
it, and then open it again. However, if I have Excel open and then go to the
Excel shortcut and open up another instance of Excel I get a message saying
the macro is read only.

This macro will be distributed to others and they may try to open another
instance of Excel and get the error. I'm not sure what I put in the statement
to have it look to see if the toolbar exists and if so not to run the macro
again and let the same toolbar work in multiple instances of Excel (as I have
it working fine in one instance with multiple workbooks). Below is the code:

Sub Auto_Open()
Call CreateToolbar
End Sub
---------------------------
Sub Auto_Close()
Call RemoveToolbar
End Sub
---------------------------
Sub RemoveToolbar()
On Error Resume Next
Application.CommandBars("Personal Macros").Delete
On Error GoTo 0
End Sub
---------------------------
'Position the toolbar in desired location
Sub PositionToolbar()
On Error Resume Next
TBar.RowIndex = Application.CommandBars("PDFMaker 7.0").RowIndex
TBar.Left = Application.CommandBars("PDFMaker 7.0").Left
On Error GoTo 0
End Sub
---------------------------
Sub CreateToolbar()
Dim TBar As CommandBar
'Dim NewDD As CommandBarControl
Dim NewBtn As CommandBarButton
Dim Menu As CommandBarPopup

'Delete any previous copy of Design toolbar
Call RemoveToolbar

'Define the Toolbar
Set TBar = CommandBars.Add
With TBar
.Name = "Personal Macros"
.Visible = True
.Position = msoBarTop
End With

'Position the toolbar
PositionToolbar

'Creates Design menu on toolbar
Set Menu = TBar.Controls.Add(Type:=msoControlPopup)
With Menu
.Caption = "Desig&n"
End With

......

And it continues adding the buttons. Also, the Auto_Close event won't work.
When I close the file it doesn't delete the toolbar. I got around this by
deleting it when excel is opened but I would rather have it do when it is
closed.

Hopefully this makes sense. Let me know if I can clarify. Thanks for the help.

 

[Dave Peterson]

First, if you are sharing this macro workbook with others, then don't name it
personal.xls. Excel can only open one file with that name at a time. If the
recipients have a personal.xls file already in use, then they'll have to make a
choice which to use.

I'd name it
SpencerUtils.xls

Then you could mark the file Readonly (using windows explorer). Then it can be
opened in lots of instances and excel will respect that windows setting and
won't ever warn you.

When I do this kind of thing, I actually save the workbook as an addin. And I
don't have to worry about the readonly business at all--no matter how many
sessions are opened.

========
And you're sure you put the Auto_Close routine in a general module?

Are you manually closing the workbook?

One more thought--are you sure that you don't see the remnants of previous
toolbars that weren't deleted during your testing phase?

 

[Spencer]

Dave, thanks for your quick response.

I have renamed the file as suggested and changed the file to read only and
that took care of the issue of opening multiple instances of Excel as you
said.

I do have the Auto_Close event in a general module, the same way I do the
Auto_Open (which works fine), but it isn't working. I am closing the workbook
manually (clicking on the red X). Does this require diffent coding?

I did have remenants of old toolbars but I deleted all of those (it was
naming them all "Custom x").

Last, I'm not real familiar with VBE or with add ins. I kind of hack my way
through it using this discussion blog. How do I turn the macro into an add in
that will make a toolbar when it is loaded? I know how to save it as an add
in and when I go to add ins under "Tools" it is there but when I select it
nothing happens.

Thanks for your help.

 

[Dave Peterson]

I'd add a msgbox to the Auto_close procedure for testing.

Msgbox "Auto_close is running"

Just to see if it's working ok and something else is going wrong. (There's
nothing special you should have to do.)

You can save a file as an addin by:
File|SaveAs|Save As Type: Microsoft Office Excel Add-In (*.xla)
(at the bottom of that dropdown)
(and xl2003 menus)

Your code should work ok if it's a plain old workbook or an addin. The biggest
difference is that the addin won't be visible to the user (and you lose the need
to mark it readonly).