Solved: problem with .clear when combined to function with.rowsource

[lehgzil]

how come .clear produces a runtime error when i use .rowsource on WITH cluster like below?
[VBA]Private Sub combobox1_Change()

With Me.ComboBox2
'value of a1 = a, a2 = b
'value of b1 = 1, b2 = 2
.Clear

Select Case ComboBox1.Value

Case "A"
.RowSource = "sheet2!a1:a2"


Case "B"
.RowSource = "sheet2!b1:b2"

End Select
End With
End Sub


Private Sub ComboBox2_Change()
If ComboBox1.Value = "A" Then
With Me.ComboBox4
.Clear
Select Case ComboBox2.Value
Case "a"
.RowSource = "sheet2!c1:c2"
Case "b"
.RowSource = "Sheet2!d1:d2"
End Select
End With
End If
If ComboBox1.Value = "B" Then
With Me.ComboBox4
.Clear
Select Case ComboBox2.Value
Case "1"
.RowSource = "sheet2!e1:e2"
Case "2"
.RowSource = "sheet2!f1:f2"
End Select
End With
End If
End Sub

Private Sub UserForm_Initialize()
'value of g1 = A, g2 = B
With Me.ComboBox1
.RowSource = "sheet2!G1:G2"
End With

End Sub[/VBA]
when combobox1 was changed it should clear combobox2 and combobox4, and same goes with combobox2 should only clear combobox4?
i tried with .additem(example on my previous post 3 multilink comboboxes with codes of sir xld) and it works fine.
is there a problem with my code or .clear and .rowsource are not compatible?
thanks.

[Bob Phillips]

When you set the rowsource of a combobox, you are binding it to a worksheet range. Thus, you cannot change the combobox directly, you have have to change the bound data, the range.

[lehgzil]

aha... ok i get it... inserted something like this..
[vba]Private Sub combobox1_Change()
combobox2.value = ""
With Me.ComboBox2
'value of a1 = a, a2 = b
'value of b1 = 1, b2 = 2
'.Clear
Select Case ComboBox1.Value
Case "A"
.RowSource = "sheet2!a1:a2"
Case "B"
.RowSource = "sheet2!b1:b2"
End Select
End With
End Sub

Private Sub ComboBox2_Change()
'combobox4.value = ""
If ComboBox1.Value = "A" Then
With Me.ComboBox4
'.Clear
Select Case ComboBox2.Value
Case "a"
.RowSource = "sheet2!c1:c2"
Case "b"
.RowSource = "Sheet2!d1:d2"
End Select
End With
End If
If ComboBox1.Value = "B" Then
With Me.ComboBox4
'.Clear
Select Case ComboBox2.Value
Case "1"
.RowSource = "sheet2!e1:e2"
Case "2"
.RowSource = "sheet2!f1:f2"
End Select
End With
End If
End Sub

Private Sub UserForm_Initialize()
'value of g1 = A, g2 = B
With Me.ComboBox1
.RowSource = "sheet2!G1:G2"
End With
End Sub [/vba]

[lehgzil]

^up
yes its a correct edit, sorry aussie, im not aware of the 20 minute limit on editing.

anyways id like to add this.

following your suggestion sir xld
first i inserted a value for the combobox then followed by the if`s. somehow it works fine as it has no bound value before the conditional statements on which inputs range values. however, when i add a G3 value C which has no combobox2 and 4 values retains the previous values it possessed from the previous combobox1 value.
(example: if "A" was picked on combobox1 and and "a" was picked on combobox2 and values for that on combobox4, all values for combobox2 and 4 remains if "C" is picked on combobox1.)
and thats because, i think, its of the value i set on the clearing part of the comboboxes.
[VBA]
combobox2.value=""
combobox4.value=""[/VBA]

thanks sir xld.

now if anyone has a better solution on what i did please post here, thanks in advance.

[Bob Phillips]

Just use more cases is my suggestion

[vba]

Private Sub ComboBox2_Change()
Select Case ComboBox1.Value
Case "A"
With Me.ComboBox4
Select Case ComboBox2.Value
Case "a": .RowSource = "sheet2!c1:c2"
Case "b": .RowSource = "Sheet2!d1:d2"
End Select
End With
Case "B"
With Me.ComboBox4
Select Case ComboBox2.Value
Case "1": .RowSource = "sheet2!e1:e2"
Case "2": .RowSource = "sheet2!f1:f2"
End Select
End With
End Select
End Sub
[/vba]