Toolbar buttons

[David B. Ring]

"application.commandbars.actioncontrol" will return a CommandBarControl
object representing
the button or other control that activated the running procedure.

Dave Ring

JohnG wrote:

> Is there a way to detect which toolbar button was clicked.
> The reason I need this is because both the Print button
> and the Print Preview buttn activate the Before_Print
> event.

[Leo Heuser]

John,

Here's one way to do it. The solution works for Excel 97 and on.
I'm not certain about prior versions.

Question:
How do I prevent the code in Workbook_BeforePrint from
executing, when calling PrintPreview?

Answer:

Enter in ThisWorkbook:

Private Sub Workbook_Open()
RedefinePrintPreview
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Preview = True Then
MsgBox "You're in preview."
Else
MsgBox "You're printing."
End If
End Sub

Enter in an ordinary module:

Public Preview As Boolean

Sub RedefinePrintPreview()
'leo.heuser, May 2001
Dim CBar As CommandBar
Dim Found As CommandBarControl

For Each CBar In CommandBars
Set Found = CBar.FindControl(Id:=109, recursive:=True)
If Not Found Is Nothing Then
Found.OnAction = "Pre"
' Set Found.OnAction = "" to return to default.
End If
Next CBar

Preview = False 'Not necessary but informative :-)

End Sub

Sub Pre()
Preview = True
ActiveSheet.PrintPreview
Preview = False
End Sub

Explanation:
Preview is created as a Public variable.

In RedefinePrintPreview:

1. The PrintPreview control has an Id-number of 109
2. Each control with an Id = 109 has its OnAction set
to call the sub "Pre"

In "Pre"

1. Preview is set to True.
2. This means, that your code in BeforePrint won't fire,
since this only happens, when Preview = False.
3. The Preview-routine is called, and when closed
Preview is set to False.
Now you can use Print and be assured, that your code in
BeforePrint is executed.

--
Best regards
Leo Heuser
MVP Excel

"JohnG" <john_g...@hotmail.com> skrev i en meddelelse
news:1316801c1b662$42680c90$37ef2ecf@TKMSFTNGXA13...