vba Office Ribbon many buttons, 1 macro...

Hi All

I'm not even sure how to phrase this question.  What I want to do is have multiple buttons on my ribbon that all fire the same macro, however I want the macro to do different things depending on which button was pressed... is this possible?  What would be the best way to achieve this?

sub AllButtons ()
  Select case ?????
     case "button 1"
     ' do this

     case "button 2"
   ' do that
   ...
End Sub
LVL 15
DrTribosAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

Please refer to http://msdn.microsoft.com/en-us/library/aa942866(v=vs.80).aspx



Public Sub OnActionCallback(ByVal control As Office.IRibbonControl, _
    ByVal isPressed As Boolean)

    If (control.Id = "checkBox1") Then
        MessageBox.Show("You clicked " + control.Id)
    Else
        MessageBox.Show("You clicked a different control.")
    End If

End Sub

Open in new window


Regards
0
 
MacroShadowCommented:
The best way would be to create a separate macro for each button.
0
 
DrTribosAuthor Commented:
Noted... still interested to know if it is possible and also why it is (if it turns out to be) bad practice to use 1 macro...

thanks
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
DrTribosAuthor Commented:
Hi Rgonzo1971

Thank you - I will have a look and post back.  Cheers,
0
 
DrTribosAuthor Commented:
Ok... stuck...  I am getting an error message: "wrong number of arguments or invalid property assignment"

This is my ribbon code:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="OnLoad">

  <ribbon>
    <tabs>
      <tab id="test" label="test" keytip="T">
        <group id="myID1" label="Test MyID">
          <box id="myID2" boxStyle="vertical">
            <button id="btn1" label="Effect 1" onAction="AllButtons"/>
            <button id="btn2" label="Effect 2" onAction="AllButtons"/>
            <button id="btn3" label="Effect 3" onAction="AllButtons"/>
          </box>
	  </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Open in new window


And this is my VBA...  BTW... I created a new module and the macros were not found so I moved them and at least they were found; but is there a special place to put this code?

Public Sub OnActionCallback(ByVal control As Office.IRibbonControl, _
    ByVal isPressed As Boolean)

    If (control.ID = "btn1") Then
        MessageBox.Show ("You clicked " + control.ID)
    Else
        MessageBox.Show ("You clicked a different control.")
    End If

End Sub

Sub AllButtons()
       MsgBox "test"
End Sub 

Open in new window


Many thanks :-)
0
 
DrTribosAuthor Commented:
Um... feeling kinda silly... changed code to:

Public myRibbon As IRibbonUI

Public Sub OnLoad(ribbon As IRibbonUI)
    Set myRibbon = ribbon
MsgBox "ribbon loaded"
End Sub

Public Sub AllButtons(ByVal control As Office.IRibbonControl, _
    ByVal isPressed As Boolean)

    If (control.ID = "btn1") Then
        MessageBox.Show ("You clicked " + control.ID)
    Else
        MessageBox.Show ("You clicked a different control.")
    End If

End Sub

Open in new window


and now I am getting error / info message "argument not optional"
0
 
DrTribosAuthor Commented:
Ooo it's working!

Public myRibbon As IRibbonUI

Public Sub OnLoad(ribbon As IRibbonUI)
    Set myRibbon = ribbon
MsgBox "ribbon loaded"
End Sub

Public Sub AllButtons(control As IRibbonControl)

' Public Sub AllButtons(ByVal control As Office.IRibbonControl, ByVal isPressed As Boolean)
'
    If (control.ID = "btn1") Then
        MsgBox ("You clicked " + control.ID)
       ' returnedVal = 1
    Else
        MsgBox ("You clicked a different control.")
       ' returnedVal = 2
    End If

End Sub

Open in new window

0
 
DrTribosAuthor Commented:
Thanks, your link got me moving (slowly) in the right direction... I have much to learn about ribbon!!

Opps... for anyone watching my post above is incorrect; here is the code that actually worked...

Public Sub AllButtons(control As IRibbonControl)

' Public Sub AllButtons(ByVal control As Office.IRibbonControl, ByVal isPressed As Boolean)
'
    If (control.ID = "btn1") Then
        MsgBox ("You clicked " + control.ID)
       ' returnedVal = 1
    Else
        MsgBox ("You clicked a different control.")
       ' returnedVal = 2
    End If

End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.