CODEWITHSUNDEEP
vbaexcel
user3772099
user3772099

Reputation: 11

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

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.

Upvotes: 1

Views: 4022

Answers (2)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

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)

Upvotes: 0

Dan Wagner
Dan Wagner

Reputation: 2713

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

Upvotes: 4

Related Questions