Iterate through controls
 

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

Iterate through controls
 

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" wrote in message
...
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

Iterate through controls
 

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
...
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" wrote in message
...
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

Iterate through controls
 

Thanks for the feedback, Lee; glad to help.

--

Vasant

"lgbjr" wrote in message
...
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
...
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" wrote in message
...
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