You have answered you own question.
Your way, the toolbar is just not portable. If you create code that builds
the toolbar when the workbook opens (which can be your xla), it is part of
the workbook (the code is), and so it goes where the workbook goes.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Craig" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bob -
>
> Quick question. I have always added macro buttons and assigned macros
> based on my previous response. Just wondering what advantage it is to do
> it the way you stated. Keep in mind, I am a novice and have no formal
> programming education. Just wanting to make sure that if I continue to do
> it my way that I won't run into problems down the road.
>
> Also, I do most of my macros in a toolbar that is available in all my
> workbooks because I store the toolbar and macros in an .xla file. Do you
> see any problems with this? The only real problem I have had, is when I
> have upgraded computers in the past, I am unable to copy the toolbar to
> the new computer. I am able to copy the macros but I have to setup the
> toolbar again and assign all the macros. Not a big deal just a pin. Is
> there any other way of completing the copy?
>
> Thanks.
>
>
>
>
>
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Always best to do it dynamically
>>
>> Option Explicit
>>
>> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>> On Error Resume Next
>> Application.CommandBars("myToolbar").Delete
>> On Error GoTo 0
>> End Sub
>>
>> Private Sub Workbook_Open()
>> Dim oCB As CommandBar
>> Dim oCtl As CommandBarControl
>>
>> On Error Resume Next
>> Application.CommandBars("myToolbar").Delete
>> On Error GoTo 0
>>
>> Set oCB = Application.CommandBars.Add(Name:="myToolbar",
>> temporary:=True)
>> With oCB
>> Set oCtl = .Controls.Add(Type:=msoControlButton)
>> With oCtl
>> .BeginGroup = True
>> .Caption = "savenv"
>> .OnAction = "savenv"
>> .FaceId = 27
>> End With
>> Set oCtl = .Controls.Add(Type:=msoControlButton)
>> With oCtl
>> .Caption = "savemyprog"
>> .OnAction = "savemyprog"
>> .FaceId = 28
>> End With
>> Set oCtl = .Controls.Add(Type:=msoControlButton)
>> With oCtl
>> .Caption = "macro4"
>> .OnAction = "macro4"
>> .FaceId = 29
>> End With
>> Set oCtl = .Controls.Add(Type:=msoControlButton)
>> With oCtl
>> .Caption = "dater"
>> .OnAction = "dater"
>> .FaceId = 30
>> End With
>> .Visible = True
>> .Position = msoBarTop
>> End With
>>
>> End Sub
>>
>>
>> 'To add this, go to the VB IDE (ALT-F11 from Excel), and in
>> 'the explorer pane, select your workbook. Then select the
>> 'ThisWorkbook object (it's in Microsoft Excel Objects which
>> 'might need expanding). Double-click the ThisWorkbook and
>> 'a code window will open up. Copy this code into there,
>> 'changing the caption and action to suit.
>>
>> 'This is part of the workbook, and will only exist with the
>> 'workbook, but will be available to anyone who opens the
>> 'workbook.
>>
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>>
>>
>> "JEB" <(E-Mail Removed)> wrote in message
>> news:4F24F5B2-1DA7-4BBE-8877-(E-Mail Removed)...
>>> I'm stumped.
>>> I have created a custom toolbar in excel and would like to add
>>> additional
>>> buttons to it. I would also like to change the macro name assignment to
>>> one
>>> of the current buttons. How do I do it.
>>>
>>> Thanking you in advance.
>>>
>>> JEB
>>
>>
>
>
|
Similar Threads
