Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Excel VBA 2007 - Combo Box
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default Excel VBA 2007 - Combo Box
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.

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,344
Default Excel VBA 2007 - Combo Box
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.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default Excel VBA 2007 - Combo Box
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.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default Excel VBA 2007 - Combo Box
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.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default Excel VBA 2007 - Combo Box
never mind Shane, I got it to work with cell reference. Thanks for all your
help

"aqualibra" wrote:

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.

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,344
Default Excel VBA 2007 - Combo Box
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" wrote:

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

"aqualibra" wrote:

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.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo box output excel 2007 Dan Excel Discussion (Misc queries) 2 May 14th 10 03:49 PM
Combo box value does not update in Excel 2007 Shaun Excel Discussion (Misc queries) 4 January 20th 10 01:49 PM
Excel 2007 Combo Box questions Silvio Excel Discussion (Misc queries) 1 December 18th 09 09:41 PM
Combo box to link to SQL Database table in Excel 2007 Seok Bee Excel Discussion (Misc queries) 0 August 6th 07 08:20 AM
Excel VBA Combo Box Populating dependent on other combo box choices ikabodred Excel Programming 1 March 15th 06 03:16 PM


All times are GMT +1. The time now is 01:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement