VBA code to locate the command bar number the printer button is on

[mikeburg]

What would be the simplest VBA code to locate the command bar numbe
that the printer command button is on & put the number into
variable.

I am trying to add two command buttons immediately after the printe
command button but it's on different command bars from computer t
computer.

For example, it may be on command bar #3 on one computer but on #4 o
another computer.

Thanks. I dream of a day when I would be half as good at VBA as yo
guys. Thanks, mikebur

 

[Norman Jones]

Hi Mike,

The following version restricts operation to visible commandbars:

'=============>>
Public Sub TesterA001()
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=4)
With Ctrl
If .Parent.Visible Then
MsgBox "Commandbar: " & .Parent.Name _
& " position = " & .Index
End If
End With
Next Ctrl
End Sub
'<<=============

 

[mikeburg]

Thanks a lot.

The 1st answer gave all command bars.

The 2nd answer would not show anything. However, I do need the printe
command button's visible command bar number. Any ideas how to correc
the 2nd answer.

By the way, I am using Excel XP (version 2002, I think).

Thanks a million, mikebur

 

[Norman Jones]

Hi Mike,

The 2nd answer would not show anything. However, I do need the printer
command button's visible command bar number. Any ideas how to correct
the 2nd answer.

Testing, the second macro returned the following message:

Commandbar: Standard position: 5

 

[mikeburg]

Could you check the code on this post again? For the life of me, I
can't get it to do anything. I've tried copying it as well as retyping
it.

Sorry for the trouble. Thanks, mikeburg

 

[Norman Jones]

Hi Mike,

Could you check the code on this post again? For the life of me, I
can't get it to do anything. I've tried copying it as well as retyping
it.

The code works for me - in my previous post, I reported the msgbox response
verbatim.

Furthermore, similar code worked for you:

The only difference between the two suggested macros is that the first
reports instances of the print control on all commandbars whilst the second
reports only on visible commandbars.

Are you sure, therefore, that you have a print control on a visible
commandbar?

 

[JK]

Hi,

I used Norman's code and developed it a bit further.
It saves the Index of the Print (and checks it's not Print Preview)
into
a variable called intIndex.
I tested with Print-control in different positions in the
'Standard'-cmdBar
and it works.

***
Public Sub TesterA001()
Dim Ctrl As Office.CommandBarControl
Dim intIndex As Integer

For Each Ctrl In Application.CommandBars("Standard").Controls
If Left(Ctrl.Caption, 5) = "Print" Then
If Left(Ctrl.Caption, 7) <> "Print P" Then
intIndex = Ctrl.Index
End If
End If
Next Ctrl

End Sub
***

Hope this solves your problem

JK

 

[Norman Jones]

Hi JK,

I used Norman's code and developed it a bit further.
It saves the Index of the Print (and checks it's not Print Preview)
into
a variable called intIndex.
I tested with Print-control in different positions in the
'Standard'-cmdBar
and it works.

My code should locate instances of the Print control on any visible toolbar
whereas your code limits any search to a single toolbar. Additionally, if
the user employed a non-English language version of Excel (using an
alternative name), your code adaptation would fail to disclose any instance
of the control.

The control id for the print control (4) is unique to that control and no
confusion with the Print Preview control (whose unique id is 109) should be
possible. In any event, a search based on a unique id should be
intrinsically more reliable than a search based on the control's caption,
which depends on the version language and can be altered using VBA.

 

[JK]

Hi JK,

My code should locate instances of the Print control on any visible toolbar
whereas your code limits any search to a single toolbar. Additionally, if
the user employed a non-English language version of Excel (using an
alternative name), your code adaptation would fail to disclose any instance
of the control.

The control id for the print control (4) is unique to that control and no
confusion with the Print Preview control (whose unique id is 109) should be
possible. In any event, a search based on a unique id should be
intrinsically more reliable than a search based on the control's caption,
which depends on the version language and can be altered using VBA.

Hi Norman,

you're absolutely right. I was thinking that the need was to insert it
into the 'Standard' bar (and in English).
I also didn't remember that control id thing. Inspired by your reply I
now have the list of control IDs in my bookmarks.
But the ID for 'Print' in the 'Standard' toolbar is 2521 instead of 4
(Office 2000).
(http://support.microsoft.com/default.aspx?scid=kb;[LN];Q213552)
Weird...

Thanks anyways,

JK