Copying print command to custom menu bar

[GrahamUNC]

I've created a custom menu bar and would like to add the print option from the file menu to that bar. I've written the code to create the new menu bar but just need help with the code to assign the print option to the NewItem that i've created call "&Print". Thanks in advance.

 

[Ara]

Hey Graham,

I'm still a novice at VBA but so if this is what you want:

sub &Print()
'For WORD
Application.Dialogs(wdDialogFilePrint).Show
'For Excel
Application.Dialogs(xlDialogPrint).Show

Good luck Graham
Ara

 

[DarkSun]

Here is a big bunch of code. What you are looking for is in here.

The thing to remember about commandbars is the Dim each object correctly.

Dim newPopUpItem As CommandBarPopup
Dim PopUpItem As CommandBarPopup
Dim newControlItem As CommandBarControl
Dim PItem As CommandBarControl
Dim PBItem As CommandBarButton
Dim Found As Boolean

For Each PItem In Application.CommandBars("Tools&quot.Controls
If PItem.Caption = "Wi&zard" Then Found = True
Next

If Found = False Then
Set newPopUpItem = Application.CommandBars("Tools&quot.Controls.Add(Type:=msoControlPopup)

With newPopUpItem
.BeginGroup = True
.Caption = "Wi&zard"
End With
End If

Found = False
Set PopUpItem = Application.CommandBars("Tools&quot.Controls.Item("Wi&zard&quot

For Each PBItem In PopUpItem.Controls

If PBItem.Caption = "Lookup" Then Found = True

Next

If Found = False Then
Set newControlItem = PopUpItem.CommandBar.Controls.Add(Type:=msoControlButton)

With newControlItem
.Caption = "Create Lookup Sheet"
.FaceId = 1
.OnAction = "LookupWzd"
End With
End If

 

[DarkSun]

Ignore all that. you just need to do something like this because the Print button has an ID.

Application.CommandBars("Custom 1&quot.Controls.Add Type:=msoControlButton, Id:=4, Before:=1

 

[Aivars]

Hi!

For copy some command button you may use copy method. The following example show how to copy <Print...> button into custom menu bar.

For illustration I put to use example custom menu bar. Its structure:
Name of custom menu bar: MyCommandBar
<Close>;<Menu1> - Popup Command Bars

<Menu1> include two command buttons:
<CmdButton1> and <CmdButton2>.

You must create reference to &quot;Microsoft Office 9.0 Object Library&quot; (...\Microsoft office\Office\MS09.dll)

Procedure for copy of command button to <MyCommandBar>;<Menu1> between <CmdButton1> and <CmdButton2>:

Sub AddCommandButtonInToMenuBar()
Dim strBarName As String
Dim cmdBar As CommandBar
Dim ctlCmdButton As CommandBarButton
Dim ctlCmdMenuButton As CommandBarPopup
Dim cmdButtonNew As CommandBarButton
Dim btyControlsCount As Byte

'Set custom menu bar
Set cmdBar = CommandBars(&quot;MyCommandBar&quot.Controls(&quot;Menu1&quot.CommandBar
btyControlsCount = cmdBar.Controls.Count
'Thing to do without:copy command button with its properties
'into custom menu bar before last command button
Set ctlCmdMenuButton = CommandBars(&quot;Menu Bar&quot.Controls(&quot;File&quot
Set ctlCmdButton = ctlCmdMenuButton.Controls(&quot;Print...&quot
ctlCmdButton.Copy Bar:=cmdBar, Before:=btyControlsCount 'If parameter &quot;Before&quot; is omitted, button will be added after last command button of selected menu bar
'Change properties of new command button
Set ctlCmdButton = cmdBar.Controls(&quot;Print...&quot
ctlCmdButton.Caption = &quot;Print&quot;
ctlCmdButton.BeginGroup = True
btyControlsCount = btyControlsCount + 1 'Command buttons count with copied button
'Also you can change change some other properties of command button, too.
'Shape group line after new command button (before last one)
cmdBar.Controls(btyControlsCount).BeginGroup = True
End Sub

You must watch one's step when you define command button what you want to copy - name of them must be punctual. For get to know command buttons names you can try following method:

For Each cmdBarCustomButton In cmdBar.Controls
Debug.Print cmdBarCustomButton.Caption
Next cmdBarCustomButton

If you don't keep command button in the your custom menu bar next time, you may use delete method on close form or application:

ctlCmdButton.Delete

Good luck!
Aivars