Custom Worksheet

[jwbatey07]

I am working on building a custom worksheet for our operation. We build custom hose assemblies and there are thousands of combinations depending on the hose type, hose size, pressure, and end connection. I have successfully created a form of sorts in excel. My current working form only use Selections and the Indirect function. The issue is when you change the informtion on a fields toward the top of the form it leaves the old, possible incorrect information behind. I would also like to print the information once a form is filled out then clear the fields for the next assembly. I've stumbled on VBA and have attempted to create a cleaner and more functional form in Visual Basic. I've been able to create some of the beginning comboboxs I'm going to need. Once i get past the second combobox everything starts to go south. Unfortunately, the raw data is not the neatest nor is it simple.

THE Sample of the form

HOSE TYPE		SIZE		HOSE TYPE		HOSE SELECTION
HYDRAULIC		HYD_4		HYD_4_DOUBLE		HB6K_4
FITTING TYPE 1				FITTING TYPE 2
JIC_4_SER_2				NPT_4_SER_2
FITTING 1				FITTING 2
F2-0406-JFS				F2-0404-PF
FERRULE TYPE				FERRULE 2
NONE NEEDED				NONE NEEDED
FERRULE 1				FERRULE 2
400_B				380_B

THE CODE
Private Sub ComboBox1_Click()
Dim x As Integer
x = ComboBox1.ListIndex

Select Case x
Case Is = 0
ComboBox2.RowSource = "Hydraulic"
Case Is = 1
ComboBox2.RowSource = "air_or_water"
Case Is = 2
ComboBox2.RowSource = "fuel"
Case Is = 3
ComboBox2.RowSource = "ac"
Case Is = 4
ComboBox2.RowSource = "ptfe"

End Select

End Sub



Private Sub ComboBox2_Click()
Dim x As Integer
x = ComboBox2.ListIndex



Select Case x
Case Is = 0
ComboBox3.RowSource = "hyd_3"
Case Is = 1
ComboBox3.RowSource = "hyd_4"
Case Is = 2
ComboBox3.RowSource = "hyd_6"
Case Is = 3
ComboBox3.RowSource = "hyd_8"
Case Is = 4
ComboBox3.RowSource = "hyd_10"
Case Is = 5
ComboBox3.RowSource = "hyd_12"
Case Is = 6
ComboBox3.RowSource = "hyd_16"
Case Is = 7
ComboBox3.RowSource = "hyd_20"
Case Is = 8
ComboBox3.RowSource = "hyd_24"
Case Is = 9
ComboBox3.RowSource = "hyd_32"
Case Is = 10
ComboBox3.RowSource = "air_4"



End Select



End Sub



Private Sub ComboBox3_Click()

Dim x As Integer
x = ComboBox3.ListIndex

Select Case x
Case Is = 0
ComboBox4.RowSource = "HYD_3"
Case Is = 1
ComboBox4.RowSource = "HYD_4_SINGLE"
Case Is = 2
ComboBox4.RowSource = "hyd_4_double"
Case Is = 3
ComboBox4.RowSource = "hyd_4_100r5"
Case Is = 4
ComboBox4.RowSource = "hyd_4_100r7_non_conductive"
Case Is = 5
ComboBox4.RowSource = "hyd_4_100r8_non_conductive"



End Select

End Sub

Private Sub UserForm_Initialize()
ComboBox1.RowSource = "type"

End Sub

THE DATA

Any help someone can give me to steer me in the right direction would be greatly appricated.

Regards,

James

 

[Fluff]

Assuming those are all named ranges you can use something like

Private Sub ComboBox1_Change()
   Me.ComboBox2.Clear
   Me.ComboBox2.List = Range(Me.ComboBox1.Value).Value
End Sub

 

[jwbatey07]

Can i use that code for each combobox? The form will have 12 comboboxes, each combobox needs to have the range of values that is tied to the previous combobox. I have a lot of named ranges and they are all static.

 

[Fluff]

As long as the value in the combos is a named range, then you can do it for all of them.
Just make sure none of them use RowSource.

 

[jwbatey07]

awesome. Thank you

 

[Fluff]

You're welcome & thanks for the feedback

 

[jwbatey07]

one last question. i'm having trouble with the reset command. I've tried several variations and cannot get it to execute correct.

 

[Fluff]

What do you want it to do?

 

[jwbatey07]

i would like to have a reset command that clears all of the comboboxes.

 

[Fluff]

How about

Private Sub cmbReset_Click()
   Dim i As Long
   
   For i = 1 To 12
      Me.Controls("ComboBox" & i).Clear
   Next i
End Sub