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

P

Phantasm

Board Regular
Joined
Nov 2, 2007
Messages
58
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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
To start you can put on an "Operations" sheet the following structure:

Sheet "Operations"
[TABLE="class: grid, width: 700"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Question[/TD]
[/TR]
[TR]
[TD="align: left"]2[/TD]
[TD="align: left"]Saw[/TD]
[TD="align: left"]Do you need to add extra to the length for cleanup?[/TD]
[/TR]
[TR]
[TD="align: left"]3[/TD]
[TD="align: left"]Laser[/TD]
[TD="align: left"]Do you need a new fixture[/TD]
[/TR]
[TR]
[TD="align: left"]4[/TD]
[TD="align: left"]Mill[/TD]
[TD="align: left"]Do you need some[/TD]
[/TR]
[TR]
[TD="align: left"]5[/TD]
[TD="align: left"]Outsource[/TD]
[TD="align: left"]Do you need other[/TD]
[/TR]
</tbody>[/TABLE]

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

Code: 
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:

Code: 
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.
 
Upvote 0
DanteAmor said:
To start you can put on an "Operations" sheet the following structure:

Sheet "Operations"
[TABLE="class: grid, width: 700"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Question[/TD]
[/TR]
[TR]
[TD="align: left"]2[/TD]
[TD="align: left"]Saw[/TD]
[TD="align: left"]Do you need to add extra to the length for cleanup?[/TD]
[/TR]
[TR]
[TD="align: left"]3[/TD]
[TD="align: left"]Laser[/TD]
[TD="align: left"]Do you need a new fixture[/TD]
[/TR]
[TR]
[TD="align: left"]4[/TD]
[TD="align: left"]Mill[/TD]
[TD="align: left"]Do you need some[/TD]
[/TR]
[TR]
[TD="align: left"]5[/TD]
[TD="align: left"]Outsource[/TD]
[TD="align: left"]Do you need other[/TD]
[/TR]
</tbody>[/TABLE]

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

Code: 
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:

Code: 
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.
Click to expand...

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?
 
Last edited:
Upvote 0
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"
[TABLE="class: grid, width: 650"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Question[/TD]
[TD="align: center"]Input[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Laser[/TD]
[TD]Do you need a new fixture[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Laser[/TD]
[TD]Do you need to add extra to the length for cleanup?[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Laser[/TD]
[TD]Do you need some[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Laser[/TD]
[TD]Do you need another thing[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Mill[/TD]
[TD]Do you need a new fixture[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Mill[/TD]
[TD]Do you need to add extra to the length for cleanup?[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Outsource[/TD]
[TD]Do you need a new fixture[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Outsource[/TD]
[TD]Do you need to add extra to the length for cleanup?[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]Saw[/TD]
[TD]Do you need a new fixture[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]Saw[/TD]
[TD]Do you need to add extra to the length for cleanup?[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]Saw[/TD]
[TD]Do you need some[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]


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

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
I
  • Solved
Userform to show only specific data in combobox dependant on other combobox selection
2 3 4
Replies
35
Views
2K
dmt32
D
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
K
  • Question Question
help with a combobox and how it loads its list (its selections)
2
Replies
13
Views
1K
Anthony47
A
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top