I need help with a combobox that will show a particular frame based upon the selection and hide the others.

[Phantasm]

So what I have is a userform that have a Combobox that asks what type of operation you are doing (ie Saw, Laser, Mill, Outsource). Each one of these operation has different questions than all of the rest (Saw - Do you need to add extra to the length for cleanup? Laser - Do you need a new fixture...). The smoothest way I thought of doing this is to make a frame for each Combobox option and have all the pertinant questions asked in that frame. Then put all frames on top of each other with a blank frame hiding them on the userform. When a user selects an option in the Combobox, its frame comes to the top & all others are hidden.

The first question is does this make sense to anyone or is there a simpler way to go about this? And the other is how to make the combo box choose what frame is shown?

In the end, all of the user input is going into a form that shows the path that the job has to follow (Saw > Mill > Ship). I know I could do it using multiple userofrms, but I want my guys to be working off of one userform that basically mimics the form that is printed out in the end.

 

[DanteAmor]

To start you can put on an "Operations" sheet the following structure:

Sheet "Operations"

	A	B
1	Type	Question
2	Saw	Do you need to add extra to the length for cleanup?
3	Laser	Do you need a new fixture
4	Mill	Do you need some
5	Outsource	Do you need other

<colgroup><col><col></colgroup><tbody>
</tbody>

Then you load the 2 data (type and question) in the combo

Private Sub UserForm_Activate()
    Set h = Sheets("Operations")
    For i = 2 To h.Range("A" & Rows.Count).End(xlUp).Row
        ComboBox1.AddItem h.Cells(i, "A").Value
        ComboBox1.List(ComboBox1.ListCount - 1, 1) = h.Cells(i, "B").Value
    Next
End Sub

When you select the type in the combo, you can display the question in a label, for example:

Private Sub ComboBox1_Change()
    Label1.Caption = ""
    If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
    Label1.Caption = ComboBox1.List(ComboBox1.ListIndex, 1)
End Sub

Let me know if you have questions.

 

[Phantasm]

To start you can put on an "Operations" sheet the following structure:

Sheet "Operations"

	A	B
1	Type	Question
2	Saw	Do you need to add extra to the length for cleanup?
3	Laser	Do you need a new fixture
4	Mill	Do you need some
5	Outsource	Do you need other

<colgroup><col><col></colgroup><tbody>
</tbody>

Then you load the 2 data (type and question) in the combo

Private Sub UserForm_Activate()
    Set h = Sheets("Operations")
    For i = 2 To h.Range("A" & Rows.Count).End(xlUp).Row
        ComboBox1.AddItem h.Cells(i, "A").Value
        ComboBox1.List(ComboBox1.ListCount - 1, 1) = h.Cells(i, "B").Value
    Next
End Sub

When you select the type in the combo, you can display the question in a label, for example:

Private Sub ComboBox1_Change()
    Label1.Caption = ""
    If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
    Label1.Caption = ComboBox1.List(ComboBox1.ListIndex, 1)
End Sub

Let me know if you have questions.

The problem with this is some operations have multiple questions & some of those have inputs from the user (length added..). Another rout I was thinking about going is with a Multipage control that has all of the operations as tabs. Would that be a better way?

 

[DanteAmor]

The best thing, for me, is to have a catalog of operations and questions or possible questions, all stored on a sheet. It would be simpler to update the catalog on the sheet than inside the userform with controls or pages.
For example if you create a new operation, with your idea you would have to create a page in the userform with all its controls and the code for the new page to work. With my idea you just have to add the record in the operations sheet.

Continued with my idea

Sheet "Operations"

	A	B	C
1	Type	Question	Input
2	Laser	Do you need a new fixture	Y
3	Laser	Do you need to add extra to the length for cleanup?	Y
4	Laser	Do you need some	N
5	Laser	Do you need another thing	N
6	Mill	Do you need a new fixture	Y
7	Mill	Do you need to add extra to the length for cleanup?	Y
8	Outsource	Do you need a new fixture	Y
9	Outsource	Do you need to add extra to the length for cleanup?	Y
10	Saw	Do you need a new fixture	Y
11	Saw	Do you need to add extra to the length for cleanup?	Y
12	Saw	Do you need some	N

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Now you need a combobox2 for the questions and textbox1 for the input

Private Sub ComboBox1_Change()
    '
    'When you select an operation, the different questions are loaded in the combo2
    '
    ComboBox2.Value = ""    'combo to questions
    ComboBox2.Clear
    TextBox1.Value = ""     'to input from user
    If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
    Set h = Sheets("Operations")
    For i = 2 To h.Range("A" & Rows.Count).End(xlUp).Row
        If h.Cells(i, "A").Value = ComboBox1.Value Then
            ComboBox2.AddItem h.Cells(i, "B").Value
        End If
    Next
End Sub
'
Private Sub CommandButton1_Click()
    '
    'validate input from user
    '
    Set h = Sheets("Operations")
    For i = 2 To h.Range("A" & Rows.Count).End(xlUp).Row
        If h.Cells(i, "A").Value = ComboBox1.Value And _
           h.Cells(i, "B").Value = ComboBox2.Value And _
           h.Cells(i, "C").Value = "Y" Then
            If TextBox1.Value = "" Then
                MsgBox "Entry is required for this operation"
                TextBox1.SetFocus
                Exit Sub
            End If
        End If
    Next
    '
    'Any other code goes here
End Sub
'
Private Sub UserForm_Activate()
    '
    'Load combobox1 whit operations
    '
    Set h = Sheets("Operations")
    For i = 2 To h.Range("A" & Rows.Count).End(xlUp).Row
        Call Agregar(ComboBox1, h.Cells(i, "A").Value)
    Next
End Sub
'
Sub Agregar(combo As ComboBox, dato As String)
    'add unique item
    For i = 0 To combo.ListCount - 1
        Select Case StrComp(combo.List(i), dato, vbTextCompare)
            Case 0: Exit Sub
            Case 1: combo.AddItem dato, i: Exit Sub
        End Select
    Next
    combo.AddItem dato
End Sub

Test in a new userform and we will strengthen what you need