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

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

"Brian" wrote in message
...
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

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


--
Cheers,
Ryan


"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

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

--
jb


"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

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

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

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

"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

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


"Brian" wrote:

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

"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

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


"john" wrote:

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


"Brian" wrote:

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

"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

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

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

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

In my original post all you had to do is enter the rest of your combobox
names into the collection like so.

Private Sub UserForm_Initialize()

Dim colComboBoxes As Collection
Dim cbo As Object

' build a collection of your comboboxes
Set colComboBoxes = New Collection
With colComboBoxes
.Add Type_Work_601
.Add Type_Work_602
.Add Type_Work_603
.Add Type_Work_604
.Add Type_Work_605
.Add Type_Work_606
.Add Type_Work_607
.Add Type_Work_608
.Add Type_Work_609
.Add Type_Work_610
.Add Type_Work_611
.Add Type_Work_612
.Add Type_Work_613
.Add Type_Work_614
.Add Type_Work_615
.Add Type_Work_616
.Add Type_Work_617
.Add Type_Work_618
.Add Type_Work_619
.Add Type_Work_620
.Add Type_Work_621
.Add Type_Work_622
.Add Type_Work_623
.Add Type_Work_701
.Add Type_Work_702
.Add Type_Work_703
.Add Type_Work_704
.Add Type_Work_705
.Add Type_Work_706
.Add Type_Work_707
.Add Type_Work_708
.Add Type_Work_709
.Add Type_Work_710
.Add Type_Work_711
.Add Type_Work_712
.Add Type_Work_713
.Add Type_Work_714
.Add Type_Work_715
.Add Type_Work_716
.Add Type_Work_717
.Add Type_Work_718
.Add Type_Work_719
.Add Type_Work_720
.Add Type_Work_721
.Add Type_Work_722
.Add Type_Work_723
End With

' loop thru each item in collection and give these values
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

But this is a little cumbersome. So I condensed the code down to fill the
collection of you comboboxes using a loop.


Private Sub UserForm_Initialize()

Dim colComboBoxes As Collection
Dim i As Long
Dim cbo As Object

' build your collection of comboboxes
Set colComboBoxes = New Collection
With colComboBoxes
For i = 601 To 723
.Add Controls("Type_Work_" & i)
Next i
For i = 701 To 723
.Add Controls("Type_Work_" & i)
Next i
End With

' give your comboboxes values
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


Let me know if this helps clarify my code. If so, click "YES" below.
--
Cheers,
Ryan


"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

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


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

--

Dave Peterson

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



"Dave Peterson" wrote:

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


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

--

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

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
on it. (Maybe you're using multipages so that they don't appear all at one
time???).



Brian wrote:

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

"Dave Peterson" wrote:

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


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

--

Dave Peterson
.


--

Dave Peterson

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.

"Dave Peterson" wrote:

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


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

--

Dave Peterson
.

Experimenting and lurking in the newsgroups is a good way to learn. That's what
I did.

If you like reference books...

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's books are very good to start.

Professional Excel Development by Stephen Bullen, Rob Bovey, John Green is
pretty advanced, but very good.

See if you can find them in your local bookstore/internet site and you can
choose what one(s) you like best.

Brian wrote:

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.

"Dave Peterson" wrote:

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


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

--

Dave Peterson
.


--

Dave Peterson

I took programming Logic in college. I am setting this program up from a Flow
Chart i designed prior to starting. I am just taking it 1 step at a time.

What I am doing is writing a user form that has all my Job data on it. The
reason for all the Combo Boxes is due to Work items that must be listed on
the Job.

When I am done there will 3 Workbooks (Templetes) that open automaticaly
(Installer Forms, Job Folder Label, Engineering Spec Sheet). All these
Workbooks have the Information Input on the user form in common. Up till now
I have been either Typing or Copy & Pasting this information over and over
again.

I got to thinking about all the time I was spending filling in all this
information time and time again. The best I came up with was "WHY". Why am I
doing this the hard way, automate it. I know I can do it, but that it is
going to take some time. At this point is has become a hobby to complete
this; just to do it.

Hopefully by the time I am done it works.

Again Thanks so much for all your help!


"Dave Peterson" wrote:

Experimenting and lurking in the newsgroups is a good way to learn. That's what
I did.

If you like reference books...

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's books are very good to start.

Professional Excel Development by Stephen Bullen, Rob Bovey, John Green is
pretty advanced, but very good.

See if you can find them in your local bookstore/internet site and you can
choose what one(s) you like best.

Brian wrote:

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.

"Dave Peterson" wrote:

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


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

--

Dave Peterson
.


--

Dave Peterson
.