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


--
Regards,
Tom Ogilvy


"Troubled User" wrote:

> 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

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.

"Tom Ogilvy" wrote:

> 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
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Troubled User" wrote:
>
> > 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

Those are pretty simple:

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


--
Regards,
Tom Ogilvy


"Troubled User" <(E-Mail Removed)> wrote in message
news:49C6C3F6-0FE7-4EFF-9B5F-(E-Mail Removed)...
> 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.
>
> "Tom Ogilvy" wrote:
>
>> 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
>>
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Troubled User" wrote:
>>
>> > 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