Hello

I have created, using Excel VBA a userform allows users to write data into a
spefic spreadsheet. However, the form consists of about 30 textboxes. What I
would like the form to do is clear all the data from the textboxes once the
data has been submitted. Currently the form leaves whatever was last in the
textbox.

Is there any way this could be down using a for each command.

Thanks

If you name the textboxes nicely (textbox1, textbox2, ..., textbox30), you could
use code like:

dim iCtr as long
for ictr = 1 to 30
me.controls("textbox" & ictr).value = ""
next ictr

If you didn't name them nicely, but they're the only textboxes on that userform:

dim ctrl as Control
for each ctrl in me.controls
if typeof ctrl is msforms.textbox then
ctrl.value = ""
end if
next ctrl


nir020 wrote:
>
> Hello
>
> I have created, using Excel VBA a userform allows users to write data into a
> spefic spreadsheet. However, the form consists of about 30 textboxes. What I
> would like the form to do is clear all the data from the textboxes once the
> data has been submitted. Currently the form leaves whatever was last in the
> textbox.
>
> Is there any way this could be down using a for each command.
>
> Thanks

--

Dave Peterson

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/26/2007 by Joel
'

'
For Each shp In ActiveSheet.Shapes
If shp.Type = msoTextBox Then

shp.DrawingObject.Caption = ""
End If
Next shp

End Sub

"nir020" wrote:

> Hello
>
> I have created, using Excel VBA a userform allows users to write data into a
> spefic spreadsheet. However, the form consists of about 30 textboxes. What I
> would like the form to do is clear all the data from the textboxes once the
> data has been submitted. Currently the form leaves whatever was last in the
> textbox.
>
> Is there any way this could be down using a for each command.
>
> Thanks