Excel VBA: Hide Pages based on Combobox selection

Question

I have a Combobox with values: "none", "1", "2", "3" and "4". And I want to have visible the number of pages shown by the Combobox. How can I achieve that? i.e. combobox = 3 - Pages 1, 2 and 3 become visible.

Answer 1

An alternative:

Private Sub ComboBox1_Change()  ' Could also use a number spinner to control user input
Dim pgCount as Short
    ' Some data validation
    pgCount = IIf(isnumeric(ComboBox1.Text),CShort(ComboBox1.Text),0)
    pgcount = iif(pgCount >= 0 and pgCount < 5, pgCount,0)

    Me.MultiPage2(0).Visible = pgCount > 0
    Me.MultiPage2(1).Visible = pgCount > 1
    Me.MultiPage2(2).Visible = pgCount > 2
    Me.MultiPage2(3).Visible = pgCount > 3
End Sub

Data validation for user input is always important - how can you handle bad input? A better question is: how can you prevent bad input in the first place.

A little explanation:

• The data validation firstly checks if the user has put in something representing a number
• If the input resembles a number then convert it to a number. In this case converting it to an Short removes any decimal places the user may have entered.
• If the input does not resemble a number, use 0 instead (a soft fail)
• If the converted number is between 0 ("None") and 4, then OK, otherwise set it to 0 (another soft fail).
• [End of data conversion]
• The next four lines use simple Boolean logic to determine if the tab should be shown. I used your answer code to determine the logic.

Answer 2

Finally got it

Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case "none"
Me.MultiPage2(0).Visible = False
Me.MultiPage2(1).Visible = False
Me.MultiPage2(2).Visible = False
Me.MultiPage2(3).Visible = False
Case "1"
Me.MultiPage2(0).Visible = True
Me.MultiPage2(1).Visible = False
Me.MultiPage2(2).Visible = False
Me.MultiPage2(3).Visible = False
Case "2"
Me.MultiPage2(0).Visible = True
Me.MultiPage2(1).Visible = True
Me.MultiPage2(2).Visible = False
Me.MultiPage2(3).Visible = False
Case "3"
Me.MultiPage2(0).Visible = True
Me.MultiPage2(1).Visible = True
Me.MultiPage2(2).Visible = True
Me.MultiPage2(3).Visible = False
Case "4"
Me.MultiPage2(0).Visible = True
Me.MultiPage2(1).Visible = True
Me.MultiPage2(2).Visible = True
Me.MultiPage2(3).Visible = True
End Select
End Sub