Counting Controls in a sheet

V

Vikram Kohli

Hi

I want to know the number of textboxes that is placed on a sheet, and their names. How can i get the data from each control at runtime as i don't know how many controls are placed on that particular sheet?

Thanks
 
T

Tom Ogilvy

form the drawing toolbar

msgbox activesheet.Textboxes.count

for control toolbox toolbar textboxes

Dim cnt as long, obj as OleObject
cnt = 0
for each obj in Activesheet.OleObjects
if type of obj.Object is MSforms.Textbox then
cnt = cnt + 1
end if
Next
msgbox cnt

--
Regards,
Tom Ogilvy

Vikram Kohli said:
Hi,

I want to know the number of textboxes that is placed on a sheet, and
Click to expand...
their names. How can i get the data from each control at runtime as i don't
know how many controls are placed on that particular sheet?
 
B

Bob Phillips

Hi Vikram,

This should help

Dim i As Long
Dim OLEObject As Object

For Each OLEObject In ActiveSheet.OLEObjects
If TypeOf OLEObject.Object Is MSForms.TextBox Then
i = i + 1
End If
Next OLEObject
MsgBox i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Vikram Kohli said:
Hi,

I want to know the number of textboxes that is placed on a sheet, and
Click to expand...
their names. How can i get the data from each control at runtime as i don't
know how many controls are placed on that particular sheet?
 
Top