Combo Box Question

B

Brian

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
 
T

Tim Williams

AddCBItems Me.Type_Work_601
AddCBItems Me.Type_Work_701-723



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


Tim
 
R

Ryan H

You can build a collection of all your comboboxes on your userform. Then
loop thru each combobox assiging your values to them with a For...Each Loop.
Put this in your userforms Intialize_Event. You comboboxes will be filled
when your userform opens.

Private Sub UserForm_Initialize()

Dim colComboBoxes As Collection
Dim cbo As Object

Set colComboBoxes = New Collection
With colComboBoxes
.Add Type_Work_601-623
.Add Type_Work_701-723

'name other comboboxes here

End With

For Each cbo In colComboBoxes
With cbo
.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
Next cbo

End Sub
 
J

john

another way perhaps:

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

For i = LBound(myarray) To UBound(myarray)

Me.Controls(Ctl.Name).AddItem myarray(i)

Next i

Me.Controls(Ctl.Name).ListIndex = 0

End If

Next

End Sub
 
B

Brian

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.
 
J

john

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.
 
B

Brian

I am using Office 2007. Here is what i replaced my code with.


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) = "Type_Work_601" Then

With Me.Controls(Ctl.Type_Work_601)

.List = myarray()

.ListIndex = 0

End With

End If

Next

End Sub
 
J

john

Brian,
don't modify my code with your combobox names - the code as i posted gets
each combobox name on the form automatically

DELETE your modified version of my code & replace with an EXACT copy of the
code I posted - it should work ok for you.
 
B

Brian

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.
 
B

Brian

I did and all my combo boxes used the "Add, Assign, etc...). There are 10
other combo boxes on my user form that also changed when I did this.

The Combo Boxes that need this code are
Type_Work_601
Thru
Type_Work_623

Type_Work_701
Thru
Type_Work_723

Thanks
Brian
 
J

john

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
 
D

Dave Peterson

Another one:

Option Explicit
Private Sub UserForm_Initialize()

Dim cCtr As Long
Dim myArr As Variant

myArr = Array("ADD:", _
"ASSIGN:", _
"EXTEND:", _
"MODIFY:", _
"MULTIPLED", _
"REASSIGNED", _
"RECABLE", _
"RELOCATE:", _
"REMOVE:", _
"RENUMBER:", _
"REOPEN/CLOSE:", _
"RETIRE IN PLACE:", _
"VERIFY")

For cCtr = 601 To 623
Me.Controls("type_work_" & cCtr).List = myArr
Me.Controls("type_work_" & cCtr + 100).List = myArr
Next cCtr

End Sub
 
B

Brian

I want to use yours, but I was just asking why his didn't work to learn more
about this process.
 
J

john

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
 
B

Brian

Apparently there is no way to shortcut this like in Excell. Something similar
to "Type_Work_601:Type_Work_623"

I inserted it exaclty as you wrote it, substituting "myname" with my Box
names.

Case "Type_Work_601", _
"Type_Work_602", _
"Type_Work_603", _
"Type_Work_604", _
"Type_Work_605", _
"Type_Work_606", _
"Type_Work_607", _
"Type_Work_608", _
"Type_Work_609", _
"Type_Work_610"

I get a compile Error: Ambiguous Name Detected: UserForm_Initialize, that
has to be becaused I already have "UserForm_Initialize" at the top of my
code, before several other Combo Boxes.
 
J

john

Yep that works but you cannot have UserForm_Initialize procedure entered
twice behind the form

All code that initializes your form must be placed in the single procedure.
If you are unsure how to combine all your code post back with it.
 
B

Brian

That worked Perfect. Thanks

Now can I ask you to explain how it works? It worked for all the
601 To 623 & 701 To 723. I only see where 601 To 623 is referenced, so why
did it work for the 701 To 723 as well. Was it the ref to
Me.Controls("type_work_" ?

Thanks for the help
 
B

Brian

I am new to this. I have read several books, but none of them seem to use
examples of anything close to what I am try to do. Maybe you can help me,
what is the best way to learn all this.
 
D

Dave Peterson

Me refers to the object that owns the code. In this case, it's the userform.
Me.Controls("type_work_" & cCtr).List = myArr
Me.Controls("type_work_" & cCtr + 100).List = myArr
Click to expand...

If you would have had textboxes or labels that had those names (that dodn't
support .list), then this would have failed.

But since you used a nice naming convention for your comboboxes (type_work_###),
then I could refer to the comboboxes using me.controls().

And look at that second line again. I'm cycling through 601 to 623 in that
loop, but the second line adds 100 to the value in the loop. 601 and 701, 602
and 702, ..., 623 and 723.

If you break this nice naming convention (skipping numbers, deleting comboboxes,
using different names), then this would have to be changed.

But you did very good by using that naming convention.

But I'm not sure (as a user) that I'd want to see a userform with 46 comboboxes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to fill a combo box with choice from another combo box 7
Having trouble with combo boxes. 11
User form Text Box & Combo Box. 5
Add.item in combo box not working 2
combo box beginner question... 9
dynamically assigning to a combo box 1
Auto-Filling Related Combo Box Entries 0
Adding the same content to multiple combo boxes 3

Top