How to deploy custome toolbar/Menu commands in Excel?

[Hiten]

Hi

I have one application on Excel with some macros & to run those macros
i added one custome toolbar now problem comes when i want that file to
copy on another machine i have to create custome toolbar to run macros.

I want that toolbar must move with this workbook or file when ever i
copy to any machine any one knows How this happens?

Thanks
Hitendra

[Nick]

The best way is to create the toolbar with VB and then it is created when
the workbook is open.
I use this technique all the time.
It can be coded in the Workbook_Open event.

'**************************************************************
' Sample Code for CommandBar Creation
' Include a subroutine in the Workbook_Open event to create
' A custom CommandBar

Sub CreateCommandbarSample()
'
' This example includes all of the following elements
'
' Command Buttons
' Dropdown Menus
' Combo Dropdown Box

Const CStCmdBar As String = "Sample Bar"

Call DeleteCommandbar

With Application.CommandBars.Add(CStCmdBar, msoBarFloating, True, True)
.Visible = True
.Position = msoBarTop
.Protection = msoBarNoChangeVisible + msoBarNoCustomize +
msoBarNoMove
With .Controls
With .Add(msoControlButton) ' Command Button with Icon & Caption
.Style = msoButtonIconAndCaption
.Caption = "Back"
.FaceId = 132
.OnAction = "ShowMain"
.TooltipText = "Back to Main Menu"
End With
With .Add(msoControlComboBox) ' Dropdown Combo Box
.BeginGroup = True
.Style = msoComboNormal
.Caption = "Zoom"
.OnAction = "SetZoom"
.TooltipText = "Set Zoom"
.AddItem ("150%")
.AddItem ("100%")
.AddItem ("80%")
.AddItem ("70%")
.AddItem ("50%")
.ListIndex = 2
End With
With .Add(msoControlButton) ' Command Button with Icon Only
.Style = msoButtonIcon
.Caption = "ShowComments"
.FaceId = 1589
.TooltipText = "Show Help Comments"
.OnAction = "ShowComments"
End With
With .Add(msoControlButton)
.Style = msoButtonIcon
.Caption = "SaveData"
.FaceId = 271
.TooltipText = "Save data to file"
.OnAction = "SaveData"
End With
With .Add(msoControlButton)
.Style = msoButtonIcon
.Caption = "Print"
.FaceId = 4
.OnAction = "PrintActiveSheet"
.TooltipText = "Print sheet"
End With
With .Add(msoControlButton)
.Style = msoButtonIcon
.Caption = "LoadData"
.FaceId = 270
.TooltipText = "Load data from file"
.OnAction = "GetImportFile"
End With
With .Add(msoControlButton)
.BeginGroup = True
.Style = msoButtonIconAndCaption
.Caption = "Exit"
.FaceId = 29
.TooltipText = "Exit application"
.OnAction = "ExitApp"
End With
With .Add(msoControlPopup) ' Custom Menu
.BeginGroup = True
.Caption = "Setup"
.TooltipText = "Setup Application"
With .Controls
With .Add(msoControlButton) ' Custom Menu Button
.Style = msoButtonCaption
.Caption = "Import Card Data"
.TooltipText = "Update Barclaycard Data"
.OnAction = "ImportBCardData"
End With
End With
End With
End With
End With
End Sub

Sub DeleteCommandbar()
'
' Delete custom commandbar

' Prevent error if commandbar does not already exist
On Error Resume Next
CommandBars(CStCmdBar).Delete

End Sub


The OnAction property must be the name of a procedure in the workbook.

Hope this helps
Nick



"Hiten" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I have one application on Excel with some macros & to run those macros
> i added one custome toolbar now problem comes when i want that file to
> copy on another machine i have to create custome toolbar to run macros.
>
> I want that toolbar must move with this workbook or file when ever i
> copy to any machine any one knows How this happens?
>
> Thanks
> Hitendra
>

[Hiten]

thanks

without writing code is it possible to deploy that toolbar?

[Gord Dibben]

Tools>Customize>Toolbars.

Select the toolbar in question and "Attach"


Gord Dibben Excel MVP

On 13 May 2005 08:59:21 -0700, "Hiten" <[email protected]> wrote:

>thanks
>
>without writing code is it possible to deploy that toolbar?