Looping thru Multiple Comboboxes

[repcodog]

Hello:
I have no formal training, just learn from the HELP button and reading Forums.
I am using Excel 2007 at home, but save as 03 compatable for work use

I have an Attendance worksheet that I use to help make work assignments at the start of the shift. It has 68 Control ComboBoxes that allow me to choose the employee attendance status.

I tried to add a link showing worksheet, but I'm not allowed to show any links as a newbie


I have a Command Button that allows me to set employee status based upon a worksheet "tclock" that contains downloadable data to see if employee has clocked in and a worksheet "vac_fmla" that lokks to see if the employee is expected to be off

Here is my VBA code, the loop idea I came up with from a another question on the Board

When I run the Command button, the combo boxes for attendance do not update..I get the correct corresponding combobox number in "cb" and I do not get any errors

I tried Me.Controls("Combobox" & cb) in place of combox ( cb ),but get a run time error 429 active x can't creat object

The code runs correct if I take out the loop and run code in group for each combo box.. ie combobox1, combo box2 etc.., but I have 4 teams of what is shown for 68 comboboxes regulating attendance..atlot of code..much easier to maintain if I can get a loop to work..plus there are 68 more combobox that I use on the right side to set job classification for the shift

The results from setting Combobox value helps create a list that I print out showning attendance ..that code is not shown and is ok

[VBA]Private Sub CommandButton2_Click()
Dim attend3 As Variant 'Assoc absent Type.. ie, LOA, PTO
Dim attend4 As Variant 'Assoc absent status.. active =1, not active 0
Dim attend5 As Variant 'Assoc present status on tclock worksheet.. active ="I", not active ""
Dim att_pres As Variant 'Assoc present status
Dim Combobox As Control

ThisWorkbook.Names.Add Name:="VACFMLA", _
RefersTo:=Worksheets("VAC_FMLA").Range("E6:I252")

'Reset all associates based to Atendance clock in status& set empty cells to blank
Application.ScreenUpdating = False
'assoc leader
For i = 6 To 17 ' loop counter starting at 1st row of data
cb = i + 20 ' adjust for 1st comobobox name
attend_Stat = Worksheets("Line1").Cells(i, 29) ' clock#
att_pres = Worksheets("Line1").Cells(i, 25) ' name
On Error Resume Next
Err.Clear
attend3 = Application.WorksheetFunction.VLookup(attend_Stat, Worksheets("VAC_FMLA").Range("VACFMLA"), 2, False)
attend4 = Application.WorksheetFunction.VLookup(attend_Stat, Worksheets("VAC_FMLA").Range("VACFMLA"), 5, False) ' 1 = scheduled absent
attend5 = Application.WorksheetFunction.VLookup(attend_Stat, Worksheets("tclock").Range("D2:F100"), 3, False) 'I = present

If attend5 = "I" And Worksheets("Line1").Range("Y6").Value <> "" Then ' employeee is present
Combobox(cb).Value = "PRES"
ElseIf attend4 = 1 Then 'emp is scheduled / expected off
Combobox(cb).Value = attend3
ElseIf Worksheets("Line1").Range("Y6").Value = "" Then 'open / not used yet
Combobox(cb).Value = ""
Else: Combobox(cb).Value = "ABS" ' emp has not called in and was not expected to be off

End If
Next i[/VBA]

thanks for any help

[Kenneth Hobs]

When I iterate several controls, I put them into an array. At the end, I showed how to add an item and how to get that value back. The value property returns nothing unless an item was selected. Note that I set the first element of the array as 1 but the first item number for a combobox is 0.

In a Module:
[vba]Public cCB() As Control, cTB() As Control
[/vba]
In the UserForm:
[vba]Private Sub UserForm_Initialize()
FillControlArrays
End Sub

Private Sub FillControlArrays()
Dim iCB As Integer, iTB As Integer, c As Control

For Each c In Me.Controls
Select Case TypeName(c)
Case "TextBox"
iTB = iTB + 1
ReDim Preserve cTB(1 To iTB)
Set cTB(iTB) = c
Case "ComboBox"
iCB = iCB + 1
ReDim Preserve cCB(1 To iCB)
Set cCB(iCB) = c
End Select
Next c

cCB(1).AddItem "First Combo Box"
cCB(UBound(cCB)).AddItem "Last Combo Box"
MsgBox 1, , cCB(1).List(0)
MsgBox UBound(cCB), , cCB(UBound(cCB)).List(0)
End Sub[/vba]

[repcodog]

Hi Kenneth:
thanks for the response

There's alot that's new to me in your answer, so I'll spend some time today analyzing it.
Select Case is new to me, I do like it better than If Else, now that I've tried it.

One thing I can't quite grasp is setting the 1st element of the array to 1 and the combobox to 0. Shouldn't the array be set to 0 and combobox to 1? I'll research it some more and maybe I'll become more enlightened.

[Kenneth Hobs]

You can force arrays to be based on 0 or 1 by:
[VBA]Option Base 0[/VBA]
or
[vba]Option Base 1[/vba]

You can use lbound() to determine if the first element is index position 0 or 1. However, Split() makes an array as 0 based always.

At some point, you will have to deal with these types of issues. Either approach is fine. As I said earlier though, ComboBox and ListBox controls are based on 0 index. One reason why I used base 1 for my arrays was that ComboBox1 at index position 1 made sense to me. You have the method now, make it your way...