Clear Textboxes, Labels, and Checkboxes

  • Thread starter Thread starter milo
  • Start date Start date
M

milo

I have a worksheet contains about 100 textboxes, labels,
and checkboxes. I would like to clear all the text from
these text boxes and labels, and uncheck all checkboxes at
once. Is there a way to set these textboxes, labels, and
checkboxes in an array and then using a loop to clear all
the text and uncheck the checkboxes instead of writing a
code for each of them (textbox1.text = "")?

Thanks,
milo
 
Milo,

Here's a modified version of a macro that was given to me.

Sub ClearMyObjects()
Dim oleObj As OLEObject
For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSForms.Textbox Then
oleobj.Object.Text = ""
ElseIf TypeOf oleObj.Object Is MSForms.Checkbox Then
oleobj.Object.Value = False
End If
Next
End Sub

Note that my objects were from the Controls Toolbox.
It won't work for objects from the Forms Toolbar.
Tested this in Excel97 SR2 and it works.
Even works on a sheet that doesn't have any objects.
 
Back
Top