User form shows / hides combo box dependant on another combo box

B

bflare

New Member
Joined
Jul 14, 2013
Messages
7
I have a userform with a combo box where you can select various models of valves. Depending on what model you select I would like another combo box to appear which is linked to a named range. There are 6 different model number to chose from so I need 6 different combo boxes to appear & 1 free text box. I want the combo box to appear in the same place on the user form each time to prevent too much space been used. I would also like a label to appear next to the combobox but this label would also change dependant on the model selected. Is this possible?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Hi,
You should only need a second combobox - If you have 6 named ranges the second combobox can be populated with a range based on the selection of first combobox.

something like following should do this for you

Code: 
 Private Sub ComboBox1_Change()    With Me.ComboBox1
        If .ListIndex <> -1 Then
            Me.ComboBox2.List = Application.Transpose(Application.Range("Range" & .ListIndex + 1))
        Else
            Me.ComboBox2.Clear
        End If
    End With
End Sub


This just an example and assumes the ranges are Named “Range1”, “Range2” etc but you should adjust to meet specific project need as required.

Dave
 
Upvote 0
dmt32 said:
Hi,
You should only need a second combobox - If you have 6 named ranges the second combobox can be populated with a range based on the selection of first combobox.

something like following should do this for you

Code: 
 Private Sub ComboBox1_Change()    With Me.ComboBox1
        If .ListIndex <> -1 Then
            Me.ComboBox2.List = Application.Transpose(Application.Range("Range" & .ListIndex + 1))
        Else
            Me.ComboBox2.Clear
        End If
    End With
End Sub


This just an example and assumes the ranges are Named “Range1”, “Range2” etc but you should adjust to meet specific project need as required.

Dave
Click to expand...

Hi Dave thanks for your reply.
I am sorry, but I do not follow. Where do I put the code? Thecombo box that has the valve models in is called cboValveModel & the rangesthat I would like it to refer to dependant on what model is selected are:
Model selected in cboValveModel =
“RUBBER LINED” would link to range: RLShutOffPressure (E42)
“HIGH PERFORMANCE” would link to range: ColdOpTemp (E44)
“DISC SEAL HIGH PERFORMANCE” would link to range: DiscSealShutOffPressure(E46)
“METAL SEAL” would link to range: WarmOpTemp (E48)
“CRYOGENIC HP” would link to range: ColdOpTemp (E50)
“VULCANISED RUBBER” would link to range: VulRLShutOffPressure(E52)
I would also like the selection that is chosen in the 2ndcombo box to be linked to a different cell on a sheet called “INPUTS”. Thecells I have put in brackets above.
Hope this makes sense & you can assist?
Thanks
Tony.
 
Upvote 0
Hi,
the code would be placed in your forms code page

Untested but try this

Rich (BB code): 
Private Sub cboValveModel_Change()
    With Me.ComboBox2
            .Clear
            .List = Application.Range(Choose(Me.cboValveModel.ListIndex + 1, _
                                             "RLShutOffPressure", "ColdOpTemp", "DiscSealShutOffPressure", _
                                             "WarmOpTemp", "ColdOpTemp", "VulRLShutOffPressure")).Value
                                                                         
    End With
End Sub



Private Sub ComboBox2_Change()
    Index = Me.cboValveModel.ListIndex + 1
   Sheets("INPUTS").Cells(Choose(Index, 42, 44, 46, 48, 50, 52), 5).Value = Me.ComboBox2.Value
End Sub

Change ComboBox2 name as required

Dave.
 
Upvote 0
dmt32 said:
Hi,
the code would be placed in your forms code page

Untested but try this

Rich (BB code): 
Private Sub cboValveModel_Change()
    With Me.ComboBox2
            .Clear
            .List = Application.Range(Choose(Me.cboValveModel.ListIndex + 1, _
                                             "RLShutOffPressure", "ColdOpTemp", "DiscSealShutOffPressure", _
                                             "WarmOpTemp", "ColdOpTemp", "VulRLShutOffPressure")).Value
                                                                         
    End With
End Sub



Private Sub ComboBox2_Change()
    Index = Me.cboValveModel.ListIndex + 1
   Sheets("INPUTS").Cells(Choose(Index, 42, 44, 46, 48, 50, 52), 5).Value = Me.ComboBox2.Value
End Sub

Change ComboBox2 name as required

Dave.
Click to expand...

Hi Dave,

That's fantastic thanks very much!

Just 2 other things. Is it possible to have combobox2 defaulted to the first line in the range? At the moment when I switch between models in the first combo box the second combo box shows blank. I would like it to default to the first line in the range.

Also, is it possible to have a label next to the second combo box that also changes depending on what is selected in the first combobox? For instance when “RUBBER LINED” is chosen in combobox 1 I would like the label next to combobox 2 to say ” RUBBER LINED SEATING TORQUE SHUTOFF PRESSURE”. Then if “HIGH PERFORMANCE” was selected I would like the label to change to “COLD OPERATING TEMP” & so on. Is this even possible because it seems really complicated to me?
 
Upvote 0
Hi,

try adding following updates shown in RED.

Rich (BB code): 
Private Sub cboValveModel_Change()
    With Me.ComboBox2
            .Clear
            .List = Application.Range(Choose(Me.cboValveModel.ListIndex + 1, _
                                             "RLShutOffPressure", "ColdOpTemp", "DiscSealShutOffPressure", _
                                             "WarmOpTemp", "ColdOpTemp", "VulRLShutOffPressure")).Value
                                                                         
            .ListIndex = 0
    End With
End Sub


Private Sub ComboBox2_Change()
    Dim Index As Integer
    Index = Me.cboValveModel.ListIndex + 1
   Sheets("INPUTS").Cells(Choose(Index, 42, 44, 46, 48, 50, 52), 5).Value = Me.ComboBox2.Value
   
   Me.Label1.Caption = Choose(Index, "RUBBER LINED SEATING TORQUE SHUTOFF PRESSURE", "COLD OPERATING TEMP")
   
End Sub

You will need to complete the Choose List as required.

I omitted declaring Index variable just copy & paste where shown.

Dave
 
Upvote 0
dmt32 said:
Hi,

try adding following updates shown in RED.

Rich (BB code): 
Private Sub cboValveModel_Change()
    With Me.ComboBox2
            .Clear
            .List = Application.Range(Choose(Me.cboValveModel.ListIndex + 1, _
                                             "RLShutOffPressure", "ColdOpTemp", "DiscSealShutOffPressure", _
                                             "WarmOpTemp", "ColdOpTemp", "VulRLShutOffPressure")).Value

            .ListIndex = 0
    End With
End Sub


Private Sub ComboBox2_Change()
    Dim Index As Integer
    Index = Me.cboValveModel.ListIndex + 1
   Sheets("INPUTS").Cells(Choose(Index, 42, 44, 46, 48, 50, 52), 5).Value = Me.ComboBox2.Value
   
   Me.Label1.Caption = Choose(Index, "RUBBER LINED SEATING TORQUE SHUTOFF PRESSURE", "COLD OPERATING TEMP")
   
End Sub

You will need to complete the Choose List as required.

I omitted declaring Index variable just copy & paste where shown.

Dave
Click to expand...

Hi Dave
The first 2 lines in red work ok & the second combo box defaultsto the first line in the range which is great. However, as soon as I run the 3rdline in red (Me.Label18.Caption = Choose(Index, "RUBBER LINED SEATINGTORQUE SHUTOFF PRESSURE", "COLD OPERATING TEMP")

I am getting a run-time error 94 invalid use of null. Thefull line is also highlighted in yellow. You said “I omitted declaring Indexvariable just copy & paste where shown.” Was this something I was supposedto add?

Also, is it possible that when I change the first combo box so I selectanother valve model it removes the information that it’s entered in the correspondingcell so for instance if I select “Rubber Lined” from combo box 1 it would linkthe second combo box to the range called RLShutOffPressure & the value I selectedfrom there would be put into cell E42 but if I then changed my mind &selected “HIGH PERFORMANCE” from the first combo box the value in thepreviously linked E42 cell would be removed?

Thanks for all your help on this its greatly appreciated.
 
Upvote 0
Hi,
try these changes & see if they do what you want

Code: 
Dim Index As Integer
Dim Row As Long
Dim wsInputs As Worksheet


Private Sub cboValveModel_Change()
    If Index > 0 Then wsInputs.Cells(Row, 5).Value = ""
    
    Index = Me.cboValveModel.ListIndex + 1
    If Index > 0 Then
        Row = Choose(Index, 42, 44, 46, 48, 50, 52)
    With Me.ComboBox2
            .Clear
            .List = Application.Range(Choose(Index, _
                                             "RLShutOffPressure", "ColdOpTemp", "DiscSealShutOffPressure", _
                                             "WarmOpTemp", "ColdOpTemp", "VulRLShutOffPressure")).Value
                                                           
            .ListIndex = 0
    End With
    End If
End Sub


Private Sub ComboBox2_Change()
        wsInputs.Cells(Row, 5).Value = Me.ComboBox2.Value
        Me.Label1.Caption = Choose(Index, "RUBBER LINED SEATING TORQUE SHUTOFF PRESSURE", "COLD OPERATING TEMP")
End Sub


Private Sub UserForm_Initialize()
Set wsInputs = ThisWorkbook.Worksheets("INPUTS")


End Sub

Note the variables at the top - These MUST be placed at the TOP of your userforms code page OUTSIDE any procedure.


However, as soon as I run the 3rdline in red (Me.Label18.Caption = Choose(Index, "RUBBER LINED SEATINGTORQUE SHUTOFF PRESSURE", "COLD OPERATING TEMP")
I am getting a run-time error 94 invalid use of null.
Click to expand...

One reason you will get that error would be if the number of elements in the choose list is lower than the value of Index variable. Did you add to list as I stated in my post?

Dave
 
Upvote 0
dmt32 said:
Hi,
try these changes & see if they do what you want

Code: 
Dim Index As Integer
Dim Row As Long
Dim wsInputs As Worksheet


Private Sub cboValveModel_Change()
    If Index > 0 Then wsInputs.Cells(Row, 5).Value = ""
    
    Index = Me.cboValveModel.ListIndex + 1
    If Index > 0 Then
        Row = Choose(Index, 42, 44, 46, 48, 50, 52)
    With Me.ComboBox2
            .Clear
            .List = Application.Range(Choose(Index, _
                                             "RLShutOffPressure", "ColdOpTemp", "DiscSealShutOffPressure", _
                                             "WarmOpTemp", "ColdOpTemp", "VulRLShutOffPressure")).Value
                                                           
            .ListIndex = 0
    End With
    End If
End Sub


Private Sub ComboBox2_Change()
        wsInputs.Cells(Row, 5).Value = Me.ComboBox2.Value
        Me.Label1.Caption = Choose(Index, "RUBBER LINED SEATING TORQUE SHUTOFF PRESSURE", "COLD OPERATING TEMP")
End Sub


Private Sub UserForm_Initialize()
Set wsInputs = ThisWorkbook.Worksheets("INPUTS")


End Sub

Note the variables at the top - These MUST be placed at the TOP of your userforms code page OUTSIDE any procedure.




One reason you will get that error would be if the number of elements in the choose list is lower than the value of Index variable. Did you add to list as I stated in my post?

Dave
Click to expand...

Hi Dave,
Things seem to be working ok & you were right I had adifferent number of elements. There are 2 small issues. If I use the comobox cboValveModelfirst on the user form I get the run-time error 94 invalid use of null error. Butif I use any other combobox on the user form then go back to the cboValveModelit works & I get no error. Very strange? Also, I have a clear button &that seems to bring up the same error now. Here is my code on the user form:

Dim Index As Integer

Dim Row As Long
Dim wsInputs As Worksheet

……………………………………………………………………
Private Sub cboValveModel_Change()

If Index > 0Then wsInputs.Cells(Row, 5).Value = ""
Index =Me.cboValveModel.ListIndex + 1
If Index > 0Then
Row =Choose(Index, 100, 44, 100, 46, 50, 48, 48, 42, 42, 52, 100, 100, 100, 100)
With Me.ComboBox2

.Clear
.List =Application.Range(Choose(Index, "NotApp", "ColdOpTemp","NotApp", "DiscSealShutOffPressure", "ColdOpTemp","WarmOpTemp", "WarmOpTemp", "RLShutOffPressure",_
"RLShutOffPressure", "VulRLShutOffPressure","NotApp", "NotApp", "NotApp","NotApp")).Value
.ListIndex= 0


End With
End If
End Sub
……………………………………………………………………
Private Sub ComboBox2_Change()
wsInputs.Cells(Row, 5).Value = Me.ComboBox2.Value
Me.Label18.Caption= Choose(Index, "", "COLD OPERATING TEMP", "","DISC SEAL SHUT OFF PRESSURE", _
"CRYOGENICOPERATING TEMP", "METAL SEAL WARM OPERATING TEMP", "METALSEAL WARM OPERATING TEMP", "RUBBER LINED SEATING TORQUE SHUTOFFPRESSURE", "RUBBER LINED SEATING TORQUE SHUTOFF PRESSURE","VULCANISED RUBBER LINED SHUT OFF PRESSURE", "","", "", "")
End Sub

……………………………………………………………………
Private Sub UserForm_Initialize()

Set wsInputs = ThisWorkbook.Worksheets("INPUTS")
End Sub
……………………………………………………………………
Private Sub cmdClear_Click()

Dim ctl As MSForms.Control

For Each ctl InMe.Controls

Select CaseTypeName(ctl)
Case"TextBox"
ctl.Text = ""
Case"CheckBox", "OptionButton", "ToggleButton"
ctl.Value = False
Case"ComboBox", "ListBox"
ctl.ListIndex = -1
End Select
Next ctl

End Sub
……………………………………………………………………
Any idea?
Thanks Tony.

 
Upvote 0
Hi


Rich (BB code): 
Private Sub ComboBox2_Change()
        If Index < 1 Then Exit Sub
        wsInputs.Cells(Row, 5).Value = Me.ComboBox2.Value
        
        Me.Label18.Caption = Choose(Index, "", "COLD OPERATING TEMP", "", "DISC SEAL SHUT OFF PRESSURE", _
                                            "CRYOGENICOPERATING TEMP", "METAL SEAL WARM OPERATING TEMP", _
                                            "METALSEAL WARM OPERATING TEMP", _
                                            "RUBBER LINED SEATING TORQUE SHUTOFFPRESSURE", _
                                            "RUBBER LINED SEATING TORQUE SHUTOFF PRESSURE", _
                                            "VULCANISED RUBBER LINED SHUT OFF PRESSURE", "", "", "", "")
End Sub

add the line in red & see if solves your issue.

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Combo Box range where user adds items to a worksheet and they are added to combo box
Replies
3
Views
82
Micron
M
B
  • Question Question
Cannot Exit Design Mode Error
Replies
5
Views
204
bisel
B
V
  • Question Question
Userform, combobox: Repicking the same selection?
Replies
2
Views
82
vrod2000
V
K
  • Solved
VBA in User Form. Xlookup formula for Dependent Combo Box.
Replies
8
Views
288
Kscoof
K
J
  • Question Question
VBA & Combo Box (Form Control) Lock
Replies
3
Views
645
Jaafar Tribak
Jaafar Tribak

Forum statistics

Threads
1,223,885
Messages
6,175,179
Members
452,615
Latest member
bogeys2birdies

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