Hi Shane,

Thanks, this helps a lot.
I was on vacation and didnt check my mails. In your code below, is it
possible to refer to the cell (B3, B4, B5) rather than the exact variable(X1,
X2, x3) itself when combobox2 values are determined.

Thanks in advance.

Private Sub ComboBox2_Enter()
If UCase(Me.ComboBox1) = "X1" Then
Me.ComboBox2.Clear
Me.ComboBox2.AddItem (Sheets(1).[D3])
Me.ComboBox2.AddItem (Sheets(1).[D4])
ElseIf UCase(Me.ComboBox1) = "X2" Then
Me.ComboBox2.Clear
Me.ComboBox2.AddItem (Sheets(1).[D4])
Me.ComboBox2.AddItem (Sheets(1).[D5])
Me.ComboBox2.AddItem (Sheets(1).[D7])
ElseIf UCase(Me.ComboBox1) = "X3" Then
Me.ComboBox2.Clear
'....
End If
End Sub

"ShaneDevenshire" wrote:

Hi,

Modify the code as follows:

Private Sub UserForm_Initialize()
Me.ComboBox1.AddItem (Sheets(1).Range("B3"))
Me.ComboBox1.AddItem (Sheets(1).Range("B4"))
Me.ComboBox1.AddItem (Sheets(1).Range("B5"))
End Sub


Private Sub ComboBox2_Enter()
If UCase(Me.ComboBox1) = "X1" Then
Me.ComboBox2.Clear
Me.ComboBox2.AddItem (Sheets(1).[D3])
Me.ComboBox2.AddItem (Sheets(1).[D4])
ElseIf UCase(Me.ComboBox1) = "X2" Then
Me.ComboBox2.Clear
Me.ComboBox2.AddItem (Sheets(1).[D4])
Me.ComboBox2.AddItem (Sheets(1).[D5])
Me.ComboBox2.AddItem (Sheets(1).[D7])
ElseIf UCase(Me.ComboBox1) = "X3" Then
Me.ComboBox2.Clear
'....
End If
End Sub

change the sheet reference as needed.
--
Cheers,
Shane Devenshire
Microsoft Excel MVP
Join http://setiathome.berkeley.edu/ and download a free screen saver and
help search for life beyond earth.

"aqualibra" wrote:

Thanks. This is helpful, but how do I code it if the data is in excel already.


X - x1, x2, x3 range B3:B5
Y - y1, y2 , y3 range D3:D6

Thanks in advance

"ShaneDevenshire" wrote:

Here is some sample code:

Private Sub UserForm_Initialize()
Me.ComboBox1.AddItem ("X1")
Me.ComboBox1.AddItem ("X2")
End Sub


Private Sub ComboBox2_Enter()
If UCase(Me.ComboBox1) = "X1" Then
Me.ComboBox2.Clear
Me.ComboBox2.AddItem ("Y1")
Me.ComboBox2.AddItem ("Y2")
ElseIf UCase(Me.ComboBox1) = "X2" Then
Me.ComboBox2.Clear
Me.ComboBox2.AddItem ("Y2")
Me.ComboBox2.AddItem ("Y3")
Me.ComboBox2.AddItem ("Y5")
End If
End Sub

--
Cheers,
Shane Devenshire


"aqualibra" wrote:

I am new to VBA.

I have two variables X with values x1, x2, x3

and Y with values y1, y2, y3, y4



I want to create two combo boxes for each of the variables. However, the
values selected for variable Y is dependent on the values selected for
variable X. For eg: If I select x1 for X, then the only possible outcomes for
Y can by y3, y4; or is I select x2 then the only possible outcomes can be y1,
y2, y3 and if I select x3 then the any value in Y can be selected.



I want to write the code in VBA. Is this possible.