View Full Version : how to convert row range to column range?

09-27-2010, 06:00 PM
hi again,
im doing comboboxes again and im trying to have its data on column based meaning
combobox1 = z x

combobox1 value z prompt combobox2 value = a b
combobox value prompts combobox3 value = 1 3
2 4

for x = c d
5 7
6 8

on which a b c d is on column a1-d1
row 1 2 is on range a2:a5
row 2 3 is on range b2:b5
and so on..

combobox2 value is on column and below it combobox3 values on rows
based on .rowsource when you pick z combobox2 value only shows letter "a" but should be a,b and x combobox2 value should be c,d.
i got the code for 1,2,3 using .rowsource, so im asking is how to convert row range to column range?or is it possible?

Private Sub UserForm_Initialize()
With Me combobox1.value
.additem "z"
.additem "x"
End With
end sub
Private Sub combobox1_Change()

With Me.ComboBox2


Select Case ComboBox1.Value

Case "z"
.RowSource = "sheet1!a1:b1"
Case "x"
.RowSource = "sheet1!a1:b1"
end select
end sub
Private Sub combobox2_Change()

With Me.ComboBox3


Select Case ComboBox2.Value

Case "a"
.RowSource = "sheet1!a2:a5"
Case "b"
.RowSource = "sheet1!b2:b5"
Case "c"
.RowSource = "sheet1!c2:c5"
Case "d"
.RowSource = "sheet1!d1:d5"
end select
end sub


Kenneth Hobs
09-28-2010, 12:29 PM
I can not determine what you mean. Can you simplify?

Typically, one would use WorsheetFunction.Transpose(rangeInhere),

09-29-2010, 05:41 PM
ok, there are 3 comboboxes on a userform.
combobox 1 has a constant value on which combobox2 will be determined by the value of combobox1 on which combobox3 is also determined by value of combobox2. on which combobox2 values are on the range sheet1 a1 to d1.
when z is picked combobox2 will produce the value of a1 and b1. on which when a1 is picked the value of combobox3 will be a2 to a5. same goes if b1 is picked or x.

sorry for the wrong code heres the correction.
Case "x" .RowSource = "sheet1!c1:d1"
Case "d" .RowSource = "sheet1!d2:d5"

i did a work around on which i have the values of the comboboxes on different sheets. but still if its possible?please tell me... thanks ken