Excel VBA 2007 - Combo Box

A

aqualibra

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.
 
S

ShaneDevenshire

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
 
A

aqualibra

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
 
S

ShaneDevenshire

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.
 
A

aqualibra

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
Click to expand...

ShaneDevenshire said:
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 said:
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
Click to expand...
Click to expand...
 
A

aqualibra

never mind Shane, I got it to work with cell reference. Thanks for all your
help

aqualibra said:
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
Click to expand...

ShaneDevenshire said:
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 said:
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

:

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


:

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.
Click to expand...
Click to expand...
Click to expand...
 
S

ShaneDevenshire

Your welcome. Could you also mark my post as answering your question. You
do that by clicking the Yes button next to "Was this post helpful to you?" at
the bottom of a response.

--
Thanks,
Shane Devenshire


aqualibra said:
never mind Shane, I got it to work with cell reference. Thanks for all your
help

aqualibra said:
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
Click to expand...

ShaneDevenshire said:
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.

:

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

:

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


:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with creating drop down 1
Sum of row and column entries (database) 4
User Defined Array Function 6
Increaseing the number of aurguments in a function 4
Coefficients of trendline 1
Seperating data from one column into two columns 2
Averaging adjacent Columns 3
Condensing data from 3D to 2D 7

Top