Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to populate a combobox based on selection from another combobo
Hi,
I have 2 combobox with data inside. When I select an item (eg. x) from ComboBox1, I require ComboBox2 to contain/filter only related items to x. Can I confirm that I couldn't use any existing Excel functions to do that? How do I do that with macro? I tried to do that by creating a Form in macro and adding comboboxes to the Form. However I would still prefer to do it in a worksheet. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to populate a combobox based on selection from anothercombobo
On Nov 22, 10:20 am, LinnT wrote:
Hi, I have 2 combobox with data inside. When I select an item (eg. x) from ComboBox1, I require ComboBox2 to contain/filter only related items to x. Can I confirm that I couldn't use any existing Excel functions to do that? How do I do that with macro? I tried to do that by creating a Form in macro and adding comboboxes to the Form. However I would still prefer to do it in a worksheet. Thanks. Hi LinnT, The example 'Dependent Lists Country City' in this website http://www.contextures.com/excelfiles.html#DataVal uses existing Excel functions to achieve this dependency. Regards trevosef |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to populate a combobox based on selection from another com
Hi Trevosef,
Thanks! It was a big help. I have another question. Once I have made a selection in combobox1 and combobox2, when I reselected another item from combobox1, the combobox2 still show the last selected value which might not be very "friendly". How do I set combobox2 to blank when I do a reselection from combobox1? Regards. " wrote: On Nov 22, 10:20 am, LinnT wrote: Hi, I have 2 combobox with data inside. When I select an item (eg. x) from ComboBox1, I require ComboBox2 to contain/filter only related items to x. Can I confirm that I couldn't use any existing Excel functions to do that? How do I do that with macro? I tried to do that by creating a Form in macro and adding comboboxes to the Form. However I would still prefer to do it in a worksheet. Thanks. Hi LinnT, The example 'Dependent Lists Country City' in this website http://www.contextures.com/excelfiles.html#DataVal uses existing Excel functions to achieve this dependency. Regards trevosef |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to populate a combobox based on selection from another com
On Nov 22, 12:55 pm, LinnT wrote:
Hi Trevosef, Thanks! It was a big help. I have another question. Once I have made a selection in combobox1 and combobox2, when I reselected another item from combobox1, the combobox2 still show the last selected value which might not be very "friendly". How do I set combobox2 to blank when I do a reselection from combobox1? Regards. " wrote: On Nov 22, 10:20 am, LinnT wrote: Hi, I have 2 combobox with data inside. When I select an item (eg. x) from ComboBox1, I require ComboBox2 to contain/filter only related items to x. Can I confirm that I couldn't use any existing Excel functions to do that? How do I do that with macro? I tried to do that by creating a Form in macro and adding comboboxes to the Form. However I would still prefer to do it in a worksheet. Thanks. Hi LinnT, The example 'Dependent Lists Country City' in this website http://www.contextures.com/excelfiles.html#DataValuses existing Excel functions to achieve this dependency. Regards trevosef- Hide quoted text - - Show quoted text - Hi LinnT, Try it this works. Open VBE (Press Alt+F11). Double-click on Sheet1 (the sheet with the validation combo boxes). Then, Copy and Paste the following code: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim rngCountry As Range Dim rngCity As Range Set rngCountry = Sheet1.Range("B3") Set rngCity = Sheet1.Range("C3") If Target.Address = rngCountry.Address Then If Not IsEmpty(rngCity) Then rngCity.Value = "" End If End If End Sub The above assumes that country is specified in cell B3 and city in cell C3. Double-click on country (B3) should clear the value in city (C3). Regards trevosef |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to populate a combobox based on selection from another com
Hi Trevosef,
The script does not really do what I wanted. Instead of double-click I was actually thinking of on selectionchange of the combobox1. Nevertheless, thanks for the suggestion. :) Best Regards. " wrote: On Nov 22, 12:55 pm, LinnT wrote: Hi Trevosef, Thanks! It was a big help. I have another question. Once I have made a selection in combobox1 and combobox2, when I reselected another item from combobox1, the combobox2 still show the last selected value which might not be very "friendly". How do I set combobox2 to blank when I do a reselection from combobox1? Regards. " wrote: On Nov 22, 10:20 am, LinnT wrote: Hi, I have 2 combobox with data inside. When I select an item (eg. x) from ComboBox1, I require ComboBox2 to contain/filter only related items to x. Can I confirm that I couldn't use any existing Excel functions to do that? How do I do that with macro? I tried to do that by creating a Form in macro and adding comboboxes to the Form. However I would still prefer to do it in a worksheet. Thanks. Hi LinnT, The example 'Dependent Lists Country City' in this website http://www.contextures.com/excelfiles.html#DataValuses existing Excel functions to achieve this dependency. Regards trevosef- Hide quoted text - - Show quoted text - Hi LinnT, Try it this works. Open VBE (Press Alt+F11). Double-click on Sheet1 (the sheet with the validation combo boxes). Then, Copy and Paste the following code: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim rngCountry As Range Dim rngCity As Range Set rngCountry = Sheet1.Range("B3") Set rngCity = Sheet1.Range("C3") If Target.Address = rngCountry.Address Then If Not IsEmpty(rngCity) Then rngCity.Value = "" End If End If End Sub The above assumes that country is specified in cell B3 and city in cell C3. Double-click on country (B3) should clear the value in city (C3). Regards trevosef |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to populate a combobox based on selection from another com
On Nov 23, 10:58 am, LinnT wrote:
Hi Trevosef, The script does not really do what I wanted. Instead of double-click I was actually thinking of on selectionchange of the combobox1. Nevertheless, thanks for the suggestion. :) Best Regards. " wrote: On Nov 22, 12:55 pm, LinnT wrote: Hi Trevosef, Thanks! It was a big help. I have another question. Once I have made a selection in combobox1 and combobox2, when I reselected another item from combobox1, the combobox2 still show the last selected value which might not be very "friendly". How do I set combobox2 to blank when I do a reselection from combobox1? Regards. " wrote: On Nov 22, 10:20 am, LinnT wrote: Hi, I have 2 combobox with data inside. When I select an item (eg. x) from ComboBox1, I require ComboBox2 to contain/filter only related items to x. Can I confirm that I couldn't use any existing Excel functions to do that? How do I do that with macro? I tried to do that by creating a Form in macro and adding comboboxes to the Form. However I would still prefer to do it in a worksheet. Thanks. Hi LinnT, The example 'Dependent Lists Country City' in this website http://www.contextures.com/excelfile...alusesexisting Excel functions to achieve this dependency. Regards trevosef- Hide quoted text - - Show quoted text - Hi LinnT, Try it this works. Open VBE (Press Alt+F11). Double-click on Sheet1 (the sheet with the validation combo boxes). Then, Copy and Paste the following code: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim rngCountry As Range Dim rngCity As Range Set rngCountry = Sheet1.Range("B3") Set rngCity = Sheet1.Range("C3") If Target.Address = rngCountry.Address Then If Not IsEmpty(rngCity) Then rngCity.Value = "" End If End If End Sub The above assumes that country is specified in cell B3 and city in cell C3. Double-click on country (B3) should clear the value in city (C3). Regards trevosef- Hide quoted text - - Show quoted text - Hi LinnT, To clear the value in city combobox2, while in design mode, double- click on the Country combobox1 to get to the code window. In the code window, it should show combobox1 on the left box. Select the change event on the right box. It should display: Private sub combobox1_change() 'To clear city combobox2 when country combobox1 value is changed combobox2 = "" End Sub The above code assumes that country is combobox1 and city is combobox2. Hope this helps Regards Trevosef In the code window, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to populate a combobox based on selection from another com
Thanks!
" wrote: On Nov 23, 10:58 am, LinnT wrote: Hi Trevosef, The script does not really do what I wanted. Instead of double-click I was actually thinking of on selectionchange of the combobox1. Nevertheless, thanks for the suggestion. :) Best Regards. " wrote: On Nov 22, 12:55 pm, LinnT wrote: Hi Trevosef, Thanks! It was a big help. I have another question. Once I have made a selection in combobox1 and combobox2, when I reselected another item from combobox1, the combobox2 still show the last selected value which might not be very "friendly". How do I set combobox2 to blank when I do a reselection from combobox1? Regards. " wrote: On Nov 22, 10:20 am, LinnT wrote: Hi, I have 2 combobox with data inside. When I select an item (eg. x) from ComboBox1, I require ComboBox2 to contain/filter only related items to x. Can I confirm that I couldn't use any existing Excel functions to do that? How do I do that with macro? I tried to do that by creating a Form in macro and adding comboboxes to the Form. However I would still prefer to do it in a worksheet. Thanks. Hi LinnT, The example 'Dependent Lists Country City' in this website http://www.contextures.com/excelfile...alusesexisting Excel functions to achieve this dependency. Regards trevosef- Hide quoted text - - Show quoted text - Hi LinnT, Try it this works. Open VBE (Press Alt+F11). Double-click on Sheet1 (the sheet with the validation combo boxes). Then, Copy and Paste the following code: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim rngCountry As Range Dim rngCity As Range Set rngCountry = Sheet1.Range("B3") Set rngCity = Sheet1.Range("C3") If Target.Address = rngCountry.Address Then If Not IsEmpty(rngCity) Then rngCity.Value = "" End If End If End Sub The above assumes that country is specified in cell B3 and city in cell C3. Double-click on country (B3) should clear the value in city (C3). Regards trevosef- Hide quoted text - - Show quoted text - Hi LinnT, To clear the value in city combobox2, while in design mode, double- click on the Country combobox1 to get to the code window. In the code window, it should show combobox1 on the left box. Select the change event on the right box. It should display: Private sub combobox1_change() 'To clear city combobox2 when country combobox1 value is changed combobox2 = "" End Sub The above code assumes that country is combobox1 and city is combobox2. Hope this helps Regards Trevosef In the code window, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate Adjacent Cells based on Pull Down Selection | New Users to Excel | |||
Auto Populate Based on Other Selection | Excel Discussion (Misc queries) | |||
How do i populate a text box according to selection in combobox? | Excel Worksheet Functions | |||
Auto populate several cells based on a selection from drop down li | Excel Discussion (Misc queries) | |||
Having data populate text boxes based on Combobox Value | Excel Programming |