How can I select all Controls in a wooksheets

  • Thread starter Thread starter bookworm98
  • Start date Start date
B

bookworm98

If I puts the textboxes, comboboxes,... on a sheet. Is there any way i
Execel VBA to loop through these components?
Please help me if you know. Thanks a lot
 
Hi Bookworm

For Each myshape In ActiveSheet.Shapes
' Your code
Next myshape


You can use this if you want to run your code only for pictures or something else

If myshape.Type = msoOLEControlObject Then .....
If myshape.Type = msoFormControl Then ......
If myshape.Type = msoPicture Then .....
If myshape.Type = msoAutoShape Or myshape.Type = msoLine Then .....
If myshape.Type = msoTextBox Then .....

There are more options
 
If they are from the control toolbox toolbar

Dim oleObj as OleObject
for each oleObj in activesheet.OleObjects
if typeof oleObj.Object is MSForms.Textboxt then

elseif typeof oleObj.Object is MSForms.Combobox then

end if
Next

--
Regards,
Tom Ogilvy

Ron de Bruin said:
Hi Bookworm

For Each myshape In ActiveSheet.Shapes
' Your code
Next myshape


You can use this if you want to run your code only for pictures or something else

If myshape.Type = msoOLEControlObject Then .....
If myshape.Type = msoFormControl Then ......
If myshape.Type = msoPicture Then .....
If myshape.Type = msoAutoShape Or myshape.Type = msoLine Then ......
If myshape.Type = msoTextBox Then .....

There are more options
Click to expand...
 
Thank you Tom. It works!
Okie. Can you know the way to set a ListBox(Form) empty.
With the TextBox and Combox the Text property can read and write.
But with the ListBox i think it only can read?
Is there any way
 
Back
Top