How to make For Each loop to cycle through predefined set of controls

So here’s a little problem I’m having in VBA: I have 7 unbound labels on my form to represent dates. I also have a Combo Box with numbers 1-4 to represent a specific week. What I am doing is updating all the date labels to relative to a specific day, and currently updating it manually is a little inefficient:

If Me.weekCombo.Value = 1 Then
Me.d1.Caption = FormatDateTime(DateAdd("d", 1, Me.startDateList.ItemData(0)), vbLongDate)

Me.d2.Caption = FormatDateTime(DateAdd("d", 2, Me.startDateList.ItemData(0)), vbLongDate)

Me.d3.Caption = FormatDateTime(DateAdd("d", 3, Me.startDateList.ItemData(0)), vbLongDate)

Me.d4.Caption = FormatDateTime(DateAdd("d", 4, Me.startDateList.ItemData(0)), vbLongDate)

Me.d5.Caption = FormatDateTime(DateAdd("d", 5, Me.startDateList.ItemData(0)), vbLongDate)

Me.d6.Caption = FormatDateTime(DateAdd("d", 6, Me.startDateList.ItemData(0)), vbLongDate)

Me.d7.Caption = FormatDateTime(DateAdd("d", 7, Me.startDateList.ItemData(0)), vbLongDate)

ElseIf Me.weekCombo.Value = 2 Then

...

So what I want to try and do is make a set of all the date labels (d1-d7) at the top of the function and have a simple ‘for each’ loop that goes through each label in order and updates it accordingly. Pseudo code is below:

if combo = 1 then:

for i =1; i<8; i++ :

d1.caption = DateAdd("d", i, date)

else if ....

Now the only problems I have are A) I don’t know how to make the set of labels, and B) I don’t know how to make the for loop. Any help is appreciated!

submitted by /u/hquaker1
[link] [comments]

Posted on