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]

I run it an I get a error. It's the same error I've been getting everytime I've tried writting the code to clear.

 

[Fluff]

All those combo_Change events should be Combo_Click events instead.

 

[jwbatey07]

I changed over to click events and I'm getting a new error now.

 

[Fluff]

What is the value of i when you get the error?

 

[jwbatey07]

I've attempted a bunch of different variances and it appears that i can be between 1 and 8. 8 comboboxes is my max right now.

 

[Fluff]

If you only have 8 combos, then use for i=1 to 8 not 1 to 12

 

[jwbatey07]

I change it from 12 to 8 and still getting the same error.

 

[Fluff]

In that case what was the value of i when you got the error?

 

[jwbatey07]

At times it will say i = 1 and other times it will say Combobox = empty

 

[Fluff]

You seem to have lost the quotes from around ComboBox. They need to be there as shown in post#13