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

 

[jwbatey07]

That worked. I greatly appreciate your help.

 

[Fluff]

Glad to help & thanks for the feedback