VBA - Hide pages (tabs) in a frame based on Combobox selection

Question

I've got VBA code written to make a hidden tab appear based on a combo box selection. There are seven options in the combo box that each corresponds to seven hidden tabs in the frame.

Private Sub CBO_EntryType_Change()

Dim iPage As Integer

If Me.CBO_EntryType.Value = "Abstracts" Then
    iPage = 1
ElseIf CBO_EntryType.Value = "Awards" Then
    iPage = 2
ElseIf CBO_EntryType.Value = "Career Fairs" Then
    iPage = 3
ElseIf CBO_EntryType.Value = "Editorials" Then
    iPage = 4
ElseIf CBO_EntryType.Value = "Rankings" Then
    iPage = 5
ElseIf CBO_EntryType.Value = "Tradeshows" Then
    iPage = 6
ElseIf CBO_EntryType.Value = "Social Media" Then
    iPage = 7
End If

Me.MultiPage1.Pages(iPage).Visible = True
End Sub

What I seem to be having trouble with is, how do I make sure the other tabs are hidden? Since people can only click one option in the combo box, but they might click one by mistake, and then click the correct one. Only one tab should be visible based on the selected item in the combo box. The other six should be hidden.

I'm thinking a For-Each-Next loop at the end of the sub that disables any tab that doesn't match the iPage variable but I'm having difficulty figuring out how to address the frame and pages in the For Each Next loop. What would the variable declaration be?

`for i = 1 to 7` ... `.visible = (i = iPage)` .... note: `i = iPage` comparison results in true/false — jsotola, Oct 02 '17 at 23:34
****** note !!! .... tabs index in a mutiPage control is zero based ..... the first one is `MultiPage1.Pages(0)` so the loop needs to be `for i = 0 to 6` and you need to adjust your if/then/else code — jsotola, Oct 02 '17 at 23:47
Thank you! Testing out all solutions today. I'll get back to you. — Keith Dickens, Oct 03 '17 at 13:12

Tim Williams · Accepted Answer · 2017-10-03T00:22:11.853

1

Untested, so may need minor tweaks...

Private Sub CBO_EntryType_Change()

    Dim iPage, arrPages, x As Long
    arrPages = Array("Abstracts", "Awards", "Career Fairs", "Editorials", _
                      "Rankings", "Tradeshows", "Social Media")

    'find the index in the array...
    iPage = Application.Match(Me.CBO_EntryType.Value, arrPages, 0)

    'if got a match then loop over the pages and show/hide
    If Not IsError(iPage) Then
        For x = 0 To Me.MultiPage1.Pages.Count-1
            Me.MultiPage1.Pages(x).Visible = ((x+1) = iPage)
        Next x
    End If

End Sub

EDIT - @jstola and I think alike...

edited Oct 03 '17 at 00:22

answered Oct 02 '17 at 23:37

Tim Williams

154,628
8
97
125

just checked .... page index is zero based ..... can you say "inconsistency in VBA implementation?" (use mr rogers voice) .... i knew you could .... lol – jsotola Oct 02 '17 at 23:50
Thank you! Testing out all solutions today. I'll get back to you. – Keith Dickens Oct 03 '17 at 13:16
To varying degrees, everyone's answers worked. I chose this one as the best answer though because it makes adding additional tabs later easier to handle. Also, a part of the process I didn't say anything about was that there are two tabs that need to be constantly on. This code made inserting code to activate the other two tabs really easy. So thank you for your help! – Keith Dickens Oct 03 '17 at 14:25

score 1 · Answer 2 · answered Oct 03 '17 at 00:36

1

Below code assumes the Caption of the Pages in the Multipage reflects the list in CBO_EntryType:

Private Sub CBO_EntryType_Change()
    Dim iPage As Long
    For iPage = 0 To Me.MultiPage1.Pages.Count - 1
        With Me.MultiPage1.Pages(iPage)
            .Visible = (.Caption = CBO_EntryType.Value)
        End With
    Next
End Sub

answered Oct 03 '17 at 00:36

PatricK

6,375
1
21
25

Thank you! Testing out all solutions today. I'll get back to you. – Keith Dickens Oct 03 '17 at 13:15

VBA - Hide pages (tabs) in a frame based on Combobox selection

2 Answers2