Excel VBA- How to Have a Command Button Call on Multiple Macros When Clicked?

Question

I'm trying to program a Command button in Excel VBA that can call on specific macros based on the string value of a cell. I thought I can just use conditional "if" statements, but there is a bug in the program.

For example, so far I have:

Private Sub CommandButton1_Click()
    If Range("F3").Select = "Retirement Age" Then
        Call NewRA
    End If
End Sub

And I want the Button to run the macro named "NewRA" when Cell F3 equals "Retirement Age". How can I write the code appropriately to this? Thanks in advance.

Dan Wagner · Accepted Answer · 2014-06-24 16:41:19Z

4

To start, I recommend using Range("F3").Value rather than Range("F3").Select.

From there, if you plan on having more than two scripts hooked to this button, I would read up on the Select Case construct. Here's an MSDN link: http://msdn.microsoft.com/en-us/library/cy37t14y.aspx

For example:

'... do your setup here

Select Case CStr(Range("F3").Value) '<~ convert whatever is in cell F3 to a String
    Case "Retirement Age"
        Call NewRA
    Case "Another String"
        Call AnotherStringScript
    Case "Another, Even Cooler String"
        Call AnotherEvenCoolerString
    '...
    Case Else
        Msgbox ("Hey! The value in this cell doesn't match a script!")
End Select

answered Jun 24, 2014 at 16:41

Dan Wagner

2,7232 gold badges15 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Patrick Lepelletier · Accepted Answer · 2014-06-25 15:13:11Z

0

You can use Range("F3").value , or Range("F3").value2 , or Range("F3").text, or even without a dot Range("F3") , but "never" use .select or .activate (unless no other choice, wich is rare in VBA)

answered Jun 25, 2014 at 15:13

Patrick Lepelletier

1,6522 gold badges19 silver badges24 bronze badges

Collectives™ on Stack Overflow

Excel VBA- How to Have a Command Button Call on Multiple Macros When Clicked?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related