Detecting which toolbar button was pressed

[JohnG]

am passing this on because I was asked this and do not know an answer.

The reason is that both the Print button and the Print Preview button activate the Before_Print event that has been set up and what needs to take place is that it should only be run if the Print button was selected.
NOTE: Print Preview actualy calls the event twice, 1st when it is pressed and 2nd if the print option within the print previeew is selected.

[Mark O'Brien]

I'm certain this was answered on the old messageboard because I was looking to solve that but couldn't. I've had a look at the archives and can't find it. Maybe I dreamed it, but it was very smart non-intuitive solution.

[dk]

Ok,

I don't think there is a foolproof way to capture all different ways of choosing print/print preview but this should get you off to a good start.

In the VB Editor insert a class module and use this code:-

Option Explicit

Private WithEvents btnPrint As Office.CommandBarButton
Private WithEvents btnPrintPreview As Office.CommandBarButton

Private Sub btnPrint_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'Print was pressed
CancelDefault = True
MsgBox "Print was pressed but I ain't printing!"
'Do whatever you feel need
End Sub

Private Sub btnPrintPreview_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'Print preview was pressed
CancelDefault = True
MsgBox "How rude, you clicked Print Preview"
'Now do whatever you want
End Sub

Public Sub SetPrintPreview(btn As Office.CommandBarButton)
Set btnPrintPreview = btn
End Sub

Public Sub SetPrint(btn As Office.CommandBarButton)
Set btnPrint = btn
End Sub

Then in a normal module use this procedure:-

Option Explicit

Public cBar As New Class1

Sub Initiate()
Dim combar As CommandBar
Dim compop As CommandBarPopup
Dim combut As CommandBarButton

'First capture Print preview button being pressed
Set combar = Application.CommandBars("Worksheet Menu Bar")
Set compop = combar.Controls("File")
Set combut = compop.Controls(11)
cBar.SetPrintPreview combut

'Now set up print button being pressed
Set combut = compop.Controls(12)
cBar.SetPrint combut

End Sub


I've done a quick check and it captures the Print option from the File menu, Print preview from the file menu and the print preview button from the Standard toolbar. You should be able to modify the code to include the Print button from the standard toolbar (I've no idea why this isn't captured by the code as it is).

The user will still be able to do things like File, Page Setup, Print Preview. I guess you could disable the Page setup button.

Let me know how you get on,

Regards,
D

[dk]

Forgot to mention.

You'll need to run the procedure Initiate before anything will work.

Regards,
D

[Mudface]

Juan Pablo posted this in reply to a similar question from me on the old board, but this was for a custom toolbar. It may be of some help but Juan Pablo I think's your man for this. This is a copy of his answer: -

I've recently accomplished this using

Application.Caller

Try this example.

Option Explicit

Sub MakeBar()
Dim bar As CommandBar
Dim bt As CommandBarButton
Dim i As Integer

On Error Resume Next
Application.CommandBars("MyBar").Delete
On Error GoTo 0

Set bar = Application.CommandBars.Add("MyBar", msoBarFloating, False, True)
With bar
For i = 1 To 3
Set bt = .Controls.Add(msoControlButton)
With bt
.Caption = "# " & i
.Style = msoButtonCaption
.OnAction = "Test"
End With
Next i
.Visible = True
End With
End Sub

Private Sub Test()
MsgBox CommandBars("MyBar").Controls(Application.Caller(1)).Caption
End Sub

First run "MakeBar" and click any of the buttons. The caption of the button will appear. Hope this gets you there.

By the way, Application.Caller is another way of making sure a macro IS NOT run from Alt - F8, check the help to see what i mean.

Juan Pablo G.


: I've set up a sheet to run a summary on a couple of other (quarterly) sheets. My code below adds the custom menu items to the command bar. I was wondering, rather than having a load of sub's for each quarter, whether there's a way of checking which of the menu items was selected. That way I could just have one sub and could assign variables depending on which quarter and year was chosen.

[dk]

As far as I can see that doesn't satisfy any of the requirements of the original question. The reply I posted allows for capture of the user pressing print/print preview as best you can with VBA. The reply above is for a custom toolbar - not what the question was related to.

Regards,
D

[Juan Pablo González]

Sorry Mudface, have to agree with dk, that procedure runs with Custom Toolbars only.

dk, I'm very Impressed with this. I would change only this:

Set combar = Application.CommandBars("Worksheet Menu Bar")

Set compop = combar.Controls("File")

Set combut = compop.Controls(11)

With this:
Set combut = Application.CommandBars.FindControl(Id:=109)

Because, the 11 Control of File Menu in Excel XP isn't PrintPreview, but the Id, i think, is the same in all versions.

And this

'Now set up print button being pressed

Set combut = compop.Controls(12)

With

Set combut = Application.CommandBars.FindControl(Id:=4)

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting

[ This Message was edited by: Juan Pablo G. on 2002-02-22 19:38 ]

[Juan Pablo González]

Also, I see that the button in the Standard toolbar is different (The default at least) than the Print Menu in File. The Id for that button is: 2521

It would be just a matter of setting up another button in the class module to handle that one.

Page Setup ID is: 247

I love this class module !