On Mar 27, 10:07 pm, Tom Ogilvy
wrote:
maybe something like this assuming the code is in a userform module or in a
worksheet module:

' at the top of the module:
Dim Jobs(22) As String

Private Sub CommandButton1_Click()\

Dim I as long, J as long
Dim K as Long, L As Integer

Jobs(0) = "Joe"
Jobs(1) = "Bob"
Jobs(2) = "Steve"
Jobs(3) = "Ray"
Jobs(4) = "Tim"
Jobs(5) = "Jim"
Jobs(6) = "Kelly"
Jobs(7) = "Rich"
Jobs(8) = "Tom"
Jobs(9) = "David"
Jobs(10) = "John"
Jobs(11) = "Chuck"
Jobs(12) = "Tracy"
Jobs(13) = "Michelle"
Jobs(14) = "Kathy"
Jobs(15) = "Chris"
Jobs(16) = "Andrea"
Jobs(17) = "Jason"
Jobs(18) = "Yvette"
Jobs(19) = "ALex"
Jobs(20) = "Tricia"

For I = 0 To 6
ComboBox4.AddItem (Jobs(I))
Next I

For J = 7 To 12
ComboBox1.AddItem (Jobs(J))
Next J

For K = 13 To 18
ComboBox2.AddItem (Jobs(K))
Next K

For L = 19 To 20
ComboBox3.AddItem (Jobs(L))
Next L

End Sub

Private Sub CommandButton3_Click()
Dim i as Long, j as Long
dim rng1 as Range, rng2 as Range
Dim rng3 as Range, rng3a as Range
Dim rng4 as Range, rng4a as Range

set rng1 = sheet3.Range("D91")
set rng2 = Sheet1.Range("A98") ' "29 120"
set rng3 = Sheet3.Range("B91")
set rng3a = Sheet1.Range("C98:D98")
set rng4 = Sheet3.Range("C91")
set rng4a = Sheet1.Range("E98:F98")
for j = 1 to 4
set cbox = me.Controls("Combobox" & j)
for i = lbound(Jobs) to Ubound(Jobs)
if jobs(i) = cbox.Value then
rng1.offset(i,0) = jobs(i)
rng2.Value = "29 120"
rng3.offset(i,0).Value = Application.Sum(rng3a)
rng4.offset(i,0).Value = Application.Sum(rng4a)
end if
Next i, j
end sub

--
Regards,
Tom Ogilvy



" wrote:
Hello. I am new to this group so please provide some constructive
criticism if I make a mistake as I will be happy to correct any that I
might make.

I have created some combo boxes using arrays as shown below

Private Sub CommandButton1_Click()
Current_Date

Dim Jobs(22) As String
Dim I, J, K, L As Integer

Jobs(0) = "Joe"
Jobs(1) = "Bob"
Jobs(2) = "Steve"
Jobs(3) = "Ray"
Jobs(4) = "Tim"
Jobs(5) = "Jim"
Jobs(6) = "Kelly"
Jobs(7) = "Rich"
Jobs(8) = "Tom"
Jobs(9) = "David"
Jobs(10) = "John"
Jobs(11) = "Chuck"
Jobs(12) = "Tracy"
Jobs(13) = "Michelle"
Jobs(14) = "Kathy"
Jobs(15) = "Chris"
Jobs(16) = "Andrea"
Jobs(17) = "Jason"
Jobs(18) = "Yvette"
Jobs(19) = "ALex"
Jobs(20) = "Tricia"

For I = 0 To 6
ComboBox4.AddItem (Jobs(I))
Next I

For J = 7 To 12
ComboBox1.AddItem (Jobs(J))
Next J

For K = 13 To 18
ComboBox2.AddItem (Jobs(K))
Next K

For L = 19 To 20
ComboBox3.AddItem (Jobs(L))
Next L

End Sub

With these arrays, if the person using this form selects a certain
name, I want to be able to place that name in a certain cell on
another sheet of this workbook starting from cell "D91" and place any
other names in cells that, when using their index number in the array,
add that number to cell "D91" to correctly place any other name.

I started doing so using case statements as shown below

Private Sub CommandButton3_Click()
Select Case ComboBox1.Value
Case "Joe"
Sheet1.Range("A98").Value = "29 120"
Sheet3.Range("D91").Value = "Joe"
Sheet3.Range("B91").Value =
Application.WorksheetFunction.Sum(Sheet1.Range("C9 8:D98"))
Sheet3.Range("C91").Value =
Application.WorksheetFunction.Sum(Sheet1.Range("E9 8:F98"))

Case "Bob"
Sheet1.Range("A98").Value = "29 120"
Sheet3.Range("D92").Value = "Bob"
Sheet3.Range("B92").Value =
Application.WorksheetFunction.Sum(Sheet1.Range("C9 8:D98"))
Sheet3.Range("C92").Value =
Application.WorksheetFunction.Sum(Sheet1.Range("E9 8:F98"))

End Select

But I wish not to do that with all 20 names. I want to use a for next
loop to replace all the case statements as it would make for more lean
programming and a lot less work for me. Does anyone have any
suggestions?? The help would be greatly appreciated- Hide quoted text -

- Show quoted text -

In regards to the line "Set cbox = Me.Controls("Combobox" & j)",
unforutnately and for some unknown reason I get a "compile error"
"Method or data member not found". Upon further review, I noticed
that Controls collection is not available. Any reason why it would
not be available????