S
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 Autpen()
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.
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 Autpen()
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.