Clear all textboxes on a page

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I found this code out here to clear textboxes and it says I have an invalid
use of the Me keyword. I have tried to define "me" as active sheet and can't
get it to work.

Any help would be appreciated.

Dim C As MSForms.Control
For Each C In Me.Controls
If TypeOf C Is MSForms.TextBox Then
C.Text = ""
End If
Next C
 
Me is defined only if you are in a class module. The Thisworkbook,
individual sheet modules and Userform modules are class modules and me
represents the owner of the module. So you don't define it. It is defined
by excel/VBA. You just use it where appropriate if the code resides in that
module.

If the code is in a general/standard (non-class) module, (produced by
Insert=>Module in the VBE), then Me is not defined and you would be best
advised not to use it as a variable name.

It is unclear what you mean by page. Worksheet, page on a multipage in a
userform, loose terminology for a Userform???

anyway,
There is no Controls collection on a worksheet. It is only found in a
Userform, multipage or tabstrip. If you wanted to loop through textboxes
on a worksheet and they came from the Control Toolbox Toolbar

In a sheet module as an event of a commandbutton click:
Private Sub CommandButton1_Click()
Dim oleObj as OleObject
for each oleObj in Me.OleObjects
if typeof oleObj.Object is MSForms.TextBox then
oleObj.Object.Value = ""
end if
Next
end sub

In a general module:
Sub ClearTextboxes()
Dim oleObj as OleObject
for each oleObj in Activesheet.OleObjects
if typeof oleObj.Object is MSForms.TextBox then
oleObj.Object.Value = ""
end if
Next
end sub
 
Tom,

Thanks for your help. It didn't work on the page so I added a Textbox from
the Control Toolbox. That worked, so my guess is that these are Textboxes
from the drawing toolbox. Do you know how to update / edit these?

Thanks again.
 
Those are pretty simple:

Dim tbox as Textbox
for each tbox in activesheet.Textboxes
tbox.Text = ""
Next
 
Back
Top