Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox Drop Down List
I have 14 Combo Boxes on my Excel form that I want to populate with "Yes" and
"No" with the initialize event. The only way I know how to do it is to use the following code for each ComboBox. With ComboBox# .AddItem "Yes" .AddItem "No" End With Is there simpler code to accomplish this? Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox Drop Down List
You can set up a pair of cells in a column in your workbook and give them a
name such as YNList and then your code can read: Me.ComboBox#.RowSource="YNList" (of course you can skip the Me. if you want). The named list can be on any sheet in the workbook, even a hidden sheet. "Johnny" wrote: I have 14 Combo Boxes on my Excel form that I want to populate with "Yes" and "No" with the initialize event. The only way I know how to do it is to use the following code for each ComboBox. With ComboBox# .AddItem "Yes" .AddItem "No" End With Is there simpler code to accomplish this? Thank you |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox Drop Down List
If your comboboxes are named nicely:
Dim iCtr As Long For iCtr = 1 To 14 With Me.Controls("Combobox" & iCtr) .AddItem "Yes" .AddItem "No" End With Next iCtr Johnny wrote: I have 14 Combo Boxes on my Excel form that I want to populate with "Yes" and "No" with the initialize event. The only way I know how to do it is to use the following code for each ComboBox. With ComboBox# .AddItem "Yes" .AddItem "No" End With Is there simpler code to accomplish this? Thank you -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox Drop Down List
Another way perhaps.
Private Sub UserForm_Initialize() Dim ctl As Control Dim CtrlType As String For Each ctl In UserForm1.Controls CtrlType = TypeName(ctl) If CtrlType = "ComboBox" Then ctl.AddItem "Yes" ctl.AddItem "No" ctl.ListIndex = 0 End If Next ctl End Sub -- jb "Johnny" wrote: I have 14 Combo Boxes on my Excel form that I want to populate with "Yes" and "No" with the initialize event. The only way I know how to do it is to use the following code for each ComboBox. With ComboBox# .AddItem "Yes" .AddItem "No" End With Is there simpler code to accomplish this? Thank you |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox Drop Down List
'A one time loop will do
'if you are using a user form... Dim intTemp As Integer For intTemp = 1 To 14 Me.Controls("Combobox" & intTemp).AddItem "Yes" Me.Controls("Combobox" & intTemp).AddItem "No" Next 'If the comboboxes are in Activesheet Dim intTemp As Integer For intTemp = 1 To 14 ActiveSheet.OLEObjects("Combobox" & intTemp).Object.AddItem "Yes" ActiveSheet.OLEObjects("Combobox" & intTemp).Object.AddItem "No" Next If this post helps click Yes --------------- Jacob Skaria "Johnny" wrote: I have 14 Combo Boxes on my Excel form that I want to populate with "Yes" and "No" with the initialize event. The only way I know how to do it is to use the following code for each ComboBox. With ComboBox# .AddItem "Yes" .AddItem "No" End With Is there simpler code to accomplish this? Thank you |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox Drop Down List
assuming the names of the combo as:
ComboBox1, ComboBox2 ... ComboBox14 'in worksheet module Sub Inizialize_Combo() Dim oC As Object For Each oC In ActiveSheet.OLEObjects AddList oC Next End Sub 'in userform module Private Sub UserForm_Initialize() Dim oC As Object For Each oC In Me.Controls AddList oC Next End Sub 'in standard module Sub AddList(cnt As Object) Dim v Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.ignorecase = True RE.Pattern = "^combobox([1-9]|1[0-4])$" v = Array("Yes", "No") If RE.test(cnt.Name) Then If TypeName(cnt) = "ComboBox" Then cnt.List = v ElseIf TypeName(cnt) = "OLEObject" Then cnt.Object.List = v End If End If End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "Johnny" wrote: I have 14 Combo Boxes on my Excel form that I want to populate with "Yes" and "No" with the initialize event. The only way I know how to do it is to use the following code for each ComboBox. With ComboBox# .AddItem "Yes" .AddItem "No" End With Is there simpler code to accomplish this? Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ComboBox drop list when clicked on??????????? | Excel Discussion (Misc queries) | |||
Combobox List Drop on Entry | Excel Programming | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
How can I get a larger list height for a combobox while drop down ? | Excel Programming | |||
Select a value from a combobox drop down list through code | Excel Programming |