View Single Post
  #15   Report Post  
Posted to microsoft.public.excel.programming
John John is offline
external usenet poster
 
Posts: 2,069
Default 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