VBA code simplification

Question

I have the following macro

Private Sub ComboBox1_Change()

    If ComboBox1 = "Berlin" Then

    Range("C20").Activate

Else
    If ComboBox1 = "Paris" Then

    Range("C100").Activate

Else
    If ComboBox1 = "London" Then

    Range("C150").Activate

End If
End If
End If

End Sub

This macro takes the value from a dropdown menu and goes to the cell, where the value is. First question is:

How can I take the values from the cells and not write them specifically in the code?

Second question is:

How can I simplify the procedure and not write for every value an IF?

`How can I take the values from the cells?`. Are you refering to "Berlin", "Paris", etc or to "C20", "C100" etc? Anyway, I guess it depends on your data structure. For example, if cells between C20 and C100 are all empty. I think you should provide more data. Maybe a snapshot of your table. — CMArg, Jan 20 '17 at 12:43
There are two separate questions here. @Wolfie 's answer solves your problem about not writing the cell values specifically in the code, and my answer addresses not writing an `If` for every condition you want to check. Wolfie's 3rd code block is a good solution to your situation and is probably the *main* answer that you want. — elmer007, Jan 20 '17 at 12:57

score 3 · Accepted Answer · edited May 23 '17 at 12:08

First, you probably don't actually want to Activate the range! See this answer: How to avoid using Select in Excel VBA macros

Secondly, your code...

Your Code

Private Sub ComboBox1_Change()

If ComboBox1 = "Berlin" Then

    Range("C20").Activate

Else

    If ComboBox1 = "Paris" Then

       Range("C100").Activate
    Else

        If ComboBox1 = "London" Then

            Range("C150").Activate

        End If

    End If

End If

End Sub

Using ElseIf

Private Sub ComboBox1_Change()

If ComboBox1 = "Berlin" Then

    Range("C20").Activate

ElseIf ComboBox1 = "Paris" Then

    Range("C100").Activate

ElseIf ComboBox1 = "London" Then

    Range("C150").Activate

End If

End Sub

See documentation: https://msdn.microsoft.com/en-us/library/office/gg251599.aspx

Not hard-coding the values

Private Sub ComboBox1_Change()

    Dim rng as Range
    Set rng = Nothing
    Set rng = ActiveSheet.Range("C:C").Find(ComboBox1.Text)

    If Not rng Is Nothing Then
        ' As I say, you probably don't actually want to use Activate!
        rng.Activate 

    End If 

End Sub

See more about the Range object here:

https://msdn.microsoft.com/en-us/library/office/ff838238.aspx

It has useful methods like Address or Value for common use in VBA. The Find function returns a Range object or Nothing if the given value isn't found in the given range.

elmer007 · Answer 2 · 2017-01-20T12:59:18.493

How can I simplify the procedure and not write for every value an IF?

If you need to test your ComboBox repeatedly (like your If-ElseIf structure), you can use the SELECT CASE to simplify your code:

Private Sub ComboBox1_Change()

Select Case ComboBox1
    Case Is = "Berlin"
        Range("C20").Activate
    Case Is = "Paris"
        Range("C100").Activate
    Case Is = "London"
        Range("C150").Activate
    Case Else
        Range("C1").Activate
End Select

End Sub

This looks at the value of ComboBox1 and picks the appropriate section to run. For example, if ComboBox1 = "Paris", it skips to the "Paris" case and only runs that section (Range("C100").Activate).

This makes it much easier to add more items to your options, and it reduces the clutter of lots of If-ElseIf-Else lines.

Edit: As mentioned by Wujaszkun, adding the Case Else section handles a ComboBox1 value that was not one of the specified cases.

I would also add `Case Else` before `End Select` to catch all other cases. It may not be needed but it's good to have. — PSotor, Jan 20 '17 at 12:57
@Wujaszkun Good point, I've edited the question to include it now. Thanks — elmer007, Jan 20 '17 at 13:02

VBA code simplification

2 Answers2