Combo Box Question
Brian,
I have modified the code to exclude comboxes you list
Where I have "myname1" etc replace with the names of all comboboxes names
you want excluded from the array list.
Private Sub UserForm_Initialize()
Dim Ctl As Control
Dim myarray() As Variant
myarray = Array("ADD:", _
"ASSIGN:", _
"EXTEND:", _
"MODIFY:", _
"MULTIPLED", _
"REASSIGNED", _
"RECABLE", _
"RELOCATE:", _
"REMOVE:", _
"RENUMBER:", _
"REOPEN/CLOSE:", _
"RETIRE IN PLACE:", _
"VERIFY")
For Each Ctl In Me.Controls
If TypeName(Ctl) = "ComboBox" Then
Select Case Ctl.Name
Case "myname1", _
"myname2", _
"myname3", _
"myname4", _
"myname5", _
"myname6", _
"myname7", _
"myname8", _
"myname9", _
"myname10"
Case Else
With Me.Controls(Ctl.Name)
.List = myarray()
.ListIndex = 0
End With
End Select
End If
Next
End Sub
--
jb
"Brian" wrote:
I want to use yours, but I was just asking why his didn't work to learn more
about this process.
"john" wrote:
That looks like you have taken part of what Ryan H posted and made it a
separate sub which is now missing some vital components that make his code
work.
As guidance, whichever offering you intend to use you must add to your form
unmolested & complete - if you then encounter problems, post back to whoever
helped you & they can give you further guidance.
Hope helpful
--
jb
"Brian" wrote:
This code give me an "Compile Error: Expected End Sub". Why is it looking for
an End Sub.
AddCBItems Me.Type_Work_601
AddCBItems Me.Type_Work_701
(Cursor here with Error Meesage)
Sub AddCBItems(cb)
With cb
.AddItem "ADD:"
.AddItem "ASSIGN:"
.AddItem "EXTEND:"
.AddItem "MODIFY:"
.AddItem "MULTIPLED"
.AddItem "REASSIGNED"
.AddItem "RECABLE"
.AddItem "RELOCATE:"
.AddItem "REMOVE:"
.AddItem "RENUMBER:"
.AddItem "REOPEN/CLOSE:"
.AddItem "RETIRE IN PLACE:"
.AddItem "VERIFY"
End With
End Sub
I am new to this, but once i have done this function one time i will
remember it forever.
"john" wrote:
Using XP / 2003 I placed this code behind form with 46 comboboxes
& worked ok
Private Sub UserForm_Initialize()
Dim Ctl As Control
Dim myarray() As Variant
Dim i As Integer
myarray = Array("ADD:", _
"ASSIGN:", _
"EXTEND:", _
"MODIFY:", _
"MULTIPLED", _
"REASSIGNED", _
"RECABLE", _
"RELOCATE:", _
"REMOVE:", _
"RENUMBER:", _
"REOPEN/CLOSE:", _
"RETIRE IN PLACE:", _
"VERIFY")
For Each Ctl In Me.Controls
If TypeName(Ctl) = "ComboBox" Then
With Me.Controls(Ctl.Name)
.List = myarray()
.ListIndex = 0
End With
End If
Next
End Sub
it would help to know what version of excel are you using? & post code
indicating where error occured.
--
jb
"Brian" wrote:
I should have made it clear, I have 46 Combo Boxes that all need the same
choices.
My Combo Boxes are
Type_Work_601
Thru
Type_Work_623
Type_Work_701
Thru
Type_Work_723
I tried each code and they all gave me a compile error.
"Brian" wrote:
I have several Combo Boxes that need to use the same choices. Is there a way
to only use this code 1 time & all the Combo Boxes offer the same choices or
do i have to enter this code for every Combo Box?
My Combo Boxes are "Type_Work_601-623" & "Type_Work_701-723"
'Type Work to be Done
With Me.Type_Work_601
.AddItem "ADD:"
.AddItem "ASSIGN:"
.AddItem "EXTEND:"
.AddItem "MODIFY:"
.AddItem "MULTIPLED"
.AddItem "REASSIGNED"
.AddItem "RECABLE"
.AddItem "RELOCATE:"
.AddItem "REMOVE:"
.AddItem "RENUMBER:"
.AddItem "REOPEN/CLOSE:"
.AddItem "RETIRE IN PLACE:"
.AddItem "VERIFY"
End With
Thanks
Brian
|