Iterate through controls

[lgbjr]

Hi All

I have an excel worksheet with 36 comboboxes (plus some other controls). I
want to iterate through 35 of the comboboxes that have names such as D1T1,
D1T2, D1T3, D2T1, D2T2, D2T3 etc. As an example, let's say I want to clear
the contents of each:

dim d, t as integer
dim cboName as string
dim cbo as Control

for d = 1 to 7
for t = 1 to 5
cboName = "TS.D" & CStr(d) & "T" & CStr(t) 'TS.D1T1
with cbo(cboName) 'with TS.D1T1
.clear
end With
Next t
Next d

this code obviously doesn't work, but it gives you an idea of what I'm
trying to do. TS is the name of the worksheet with the controls.

TIA
Lee

[Vasant Nanavati]

Perhaps this simplified example will help:

Sub Test()
Dim t As Integer, ctl As OLEObject, cboName As String
For Each ctl In Worksheets("TS").OLEObjects
For t = 1 To 3
cboName = "ComboBox" & t
If ctl.Name = cboName Then ctl.Object.Clear
Next t
Next
End Sub

--

Vasant

"lgbjr" <[email protected]> wrote in message
news:OVOSw%[email protected]...
> Hi All
>
> I have an excel worksheet with 36 comboboxes (plus some other controls). I
> want to iterate through 35 of the comboboxes that have names such as D1T1,
> D1T2, D1T3, D2T1, D2T2, D2T3 etc. As an example, let's say I want to clear
> the contents of each:
>
> dim d, t as integer
> dim cboName as string
> dim cbo as Control
>
> for d = 1 to 7
> for t = 1 to 5
> cboName = "TS.D" & CStr(d) & "T" & CStr(t) 'TS.D1T1
> with cbo(cboName) 'with TS.D1T1
> .clear
> end With
> Next t
> Next d
>
> this code obviously doesn't work, but it gives you an idea of what I'm
> trying to do. TS is the name of the worksheet with the controls.
>
> TIA
> Lee
>
>

[lgbjr]

Vasant,

Thanks! Just a simple example gave me a push in the right direction.

Lee

"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
news:[email protected]...
> Perhaps this simplified example will help:
>
> Sub Test()
> Dim t As Integer, ctl As OLEObject, cboName As String
> For Each ctl In Worksheets("TS").OLEObjects
> For t = 1 To 3
> cboName = "ComboBox" & t
> If ctl.Name = cboName Then ctl.Object.Clear
> Next t
> Next
> End Sub
>
> --
>
> Vasant
>
> "lgbjr" <[email protected]> wrote in message
> news:OVOSw%[email protected]...
>> Hi All
>>
>> I have an excel worksheet with 36 comboboxes (plus some other controls).
>> I
>> want to iterate through 35 of the comboboxes that have names such as
>> D1T1,
>> D1T2, D1T3, D2T1, D2T2, D2T3 etc. As an example, let's say I want to
>> clear
>> the contents of each:
>>
>> dim d, t as integer
>> dim cboName as string
>> dim cbo as Control
>>
>> for d = 1 to 7
>> for t = 1 to 5
>> cboName = "TS.D" & CStr(d) & "T" & CStr(t) 'TS.D1T1
>> with cbo(cboName) 'with TS.D1T1
>> .clear
>> end With
>> Next t
>> Next d
>>
>> this code obviously doesn't work, but it gives you an idea of what I'm
>> trying to do. TS is the name of the worksheet with the controls.
>>
>> TIA
>> Lee
>>
>>
>
>

[Vasant Nanavati]

Thanks for the feedback, Lee; glad to help.

--

Vasant

"lgbjr" <[email protected]> wrote in message
news:[email protected]...
> Vasant,
>
> Thanks! Just a simple example gave me a push in the right direction.
>
> Lee
>
> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
> news:[email protected]...
> > Perhaps this simplified example will help:
> >
> > Sub Test()
> > Dim t As Integer, ctl As OLEObject, cboName As String
> > For Each ctl In Worksheets("TS").OLEObjects
> > For t = 1 To 3
> > cboName = "ComboBox" & t
> > If ctl.Name = cboName Then ctl.Object.Clear
> > Next t
> > Next
> > End Sub
> >
> > --
> >
> > Vasant
> >
> > "lgbjr" <[email protected]> wrote in message
> > news:OVOSw%[email protected]...
> >> Hi All
> >>
> >> I have an excel worksheet with 36 comboboxes (plus some other
controls).
> >> I
> >> want to iterate through 35 of the comboboxes that have names such as
> >> D1T1,
> >> D1T2, D1T3, D2T1, D2T2, D2T3 etc. As an example, let's say I want to
> >> clear
> >> the contents of each:
> >>
> >> dim d, t as integer
> >> dim cboName as string
> >> dim cbo as Control
> >>
> >> for d = 1 to 7
> >> for t = 1 to 5
> >> cboName = "TS.D" & CStr(d) & "T" & CStr(t) 'TS.D1T1
> >> with cbo(cboName) 'with TS.D1T1
> >> .clear
> >> end With
> >> Next t
> >> Next d
> >>
> >> this code obviously doesn't work, but it gives you an idea of what I'm
> >> trying to do. TS is the name of the worksheet with the controls.
> >>
> >> TIA
> >> Lee
> >>
> >>
> >
> >
>
>