View Full Version : combobox to populate listbox
Nmarkit
09-03-2013, 08:26 AM
hello
Please help.
I have an activex combo box which i am populating depending on the selection made by the user on a few radio buttons. so say if user clicks on button 1, the combobox is populated with a list of clients. if user clicks on button 2 then same combo box is populated with a different set of clients and so on. i now want to be able to take a dump of the list available in the combobox at each given time and paste it within a list box. the user will have to hit a command button to trigger this copy pasting task. can someone please advise how this can be done?
also just out of curiosity does excel/vba allow you to dump data into a new notepad? so in the above case instead of dumping data into a listbox i can have a new notepad open up and the combobox list populated in that? if this is possible please advise how.
Thanks!
Nmarkit
09-03-2013, 08:27 AM
the code to update the combobox is below
Sub OptionChange(myOption As Long)
Dim Limit As Long
Select Case myOption
'all clients
Case 1
Limit = Sheets("output").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Dashboard").ComboBox1.ListFillRange = "output!A2:A" & Limit
'increase >10%
Case 2
Limit = Sheets("output").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Dashboard").ComboBox1.ListFillRange = "output!B2:B" & Limit
'decrease >10%
Case 3
Limit = Sheets("output").Cells(Rows.Count, 3).End(xlUp).Row
Sheets("Dashboard").ComboBox1.ListFillRange = "output!C2:C" & Limit
'increase >20%
Case 4
Limit = Sheets("output").Cells(Rows.Count, 4).End(xlUp).Row
Sheets("Dashboard").ComboBox1.ListFillRange = "output!D2:D" & Limit
'decrease >20%
Case 5
Limit = Sheets("Dashboard").Cells(Rows.Count, 5).End(xlUp).Row
Sheets("Dashboard").ComboBox1.ListFillRange = "output!E2:E" & Limit
End Select
End Sub
Private Sub OptionButton4_Click()
OptionChange (4)
End Sub
Private Sub OptionButton5_Click()
OptionChange (5)
End Sub
Private Sub OptionButton6_Click()
OptionChange (1)
End Sub
Private Sub OptionButton7_Click()
OptionChange (2)
End Sub
Private Sub OptionButton8_Click()
OptionChange (3)
End Sub
Can you please use code tags !
Nmarkit
09-03-2013, 08:57 AM
Sub OptionChange(myOption As Long)
Dim Limit As Long
Select Case myOption
'all clients
Case 1
Limit = Sheets("output").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Dashboard").ComboBox1.ListFillRange = "output!A2:A" & Limit
'increase >10%
Case 2
Limit = Sheets("output").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Dashboard").ComboBox1.ListFillRange = "output!B2:B" & Limit
'decrease >10%
Case 3
Limit = Sheets("output").Cells(Rows.Count, 3).End(xlUp).Row
Sheets("Dashboard").ComboBox1.ListFillRange = "output!C2:C" & Limit
'increase >20%
Case 4
Limit = Sheets("output").Cells(Rows.Count, 4).End(xlUp).Row
Sheets("Dashboard").ComboBox1.ListFillRange = "output!D2:D" & Limit
'decrease >20%
Case 5
Limit = Sheets("Dashboard").Cells(Rows.Count, 5).End(xlUp).Row
Sheets("Dashboard").ComboBox1.ListFillRange = "output!E2:E" & Limit
End Select
End Sub
Private Sub OptionButton4_Click()
OptionChange (4)
End Sub
Private Sub OptionButton5_Click()
OptionChange (5)
End Sub
Sub OptionChange(y As Long)
Sheets("Dashboard").ComboBox1.list=Sheets("output").columns(1).specialcells(2).offset(1).specialcells(2).offset(,y-1).value
End Sub
Private Sub OptionButton4_Click()
OptionChange 4
End Sub
Nmarkit
09-04-2013, 12:48 AM
Hi snb
Thanks for your response. I'm afraid I'm not sure I understand. Do I need to replace my existing code with what you have provided? How will this populate my listbox (ListBox2)? I'm a total vba novice so excuse the silly questions :)
thanks!
I you are a novice I'd advise to start with something less complicated.
Nmarkit
09-04-2013, 01:23 AM
I need to do this as part of a project but thanks for your help and advice.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.