Detecting which toolbar button was pressed
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Detecting which toolbar button was pressed

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    SRC
    Posts
    165
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Forgot to mention.

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

    Regards,
    D

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 !
    Regards,

    Juan Pablo González
    http://www.juanpg.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com