Selecting 31 list boxes using loop on VBA

[mane_uk]

Hi all,

I am trying to do a "calendar" display type using list boxes for each day - lbxDay1, lbxDay2,..., lbxDay30, lbxDay31 - in an Excel file.

I am OK to grab the data which will populate the list boxes but I still can't find a way to specify which list box I want to include the data in without being specific on the name.

I was wondering if there is any VBA code that would loop through the list boxes or something like a For/Next which would count 1 to 31 and pick the list box. Something like the code below:

For i=1 to 31
   lbxDayi.addItem("bla bla bla")
Next

Many thanks for your help!!

 

[Jaye7]

You can modify the following script to suite what you want to do.

'To clear or change various listtboxes or buttons, first change there tags to 'an x or to any character you decide, you will need to change the x in the 'script to match your value though.  
 
For Each Ctrl In Me.Controls
        If Ctrl.Tag = "x" Then
            Select Case TypeName(Ctrl)
 
' the following will clear each listbox which has a tag of "X"

                Case "ListBox": Ctrl.Value = ""
                
 
            End Select
        End If
    Next Ctrl

 

[mane_uk]

Hi Jaye7,

Thanks for your reply.

Just a quick question, in the code you sent I would still have to specify the list box name in the Select Case, wouldn't I!?

Cheers

 

[Jaye7]

You do not have to specify the listbox as it cycles through them and looks for the Tag value of x and then it will add what ever script you specify.

It is a great tool to update multiple listboxes or textboxes.