Combo Box Question

[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]

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!

 

[Ryan H]

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.