clearing text boxes in userform 1

[yumbelie]

Okay, I thought this would be a straightforward 10 second job. I've tackled far harder - But my lack of VB experience and on hand books is making it a bit of a challenge. *All* I want to do is clear the text boxes in the userform when I've entered the values and submitted them. Simple Stuff? Should be, I've tried the following approaches:

(I got this off a tutorial website)

Private Sub CommandButton2_Click()
Set MySheet = ActiveWorkbook.ActiveSheet
Dim ctl As Control

MySheet.Cells(1, 1).Value = TextBox1
MySheet.Cells(1, 2).Value = TextBox2
MySheet.Cells(1, 3).Value = TextBox3

For Each ctl In UserForm1.Controls
If TypeOf ctl Is TextBox Then
ctl = ""
End If
Next ctl

End Sub

and (I saw similar code on another website):

Private Sub CommandButton2_Click()
Set MySheet = ActiveWorkbook.ActiveSheet
Dim ctl As Control

MySheet.Cells(1, 1).Value = TextBox1
MySheet.Cells(1, 2).Value = TextBox2
MySheet.Cells(1, 3).Value = TextBox3

For x = 1 To 3
TextBox(x) = ""
Next x

End Sub

and neither of them work, the second method doesn't work even if I try a string concatenation, like tvar = "Textbox" & x Then tvar.Value = "" - I suspect thats not a valid way of doing it, but as I say, some aspects of VB still new to me

So, if anyone could provide me with a method of clearing the boxes when I submit values, I'd be most grateful

Thanks

Joseph.

 

[xlbo]

Try this'n - courtesy of Acron replying to a very similar post of mine a while back:

For Each ctrl In Me.Controls
'Debug.Print TypeName(ctrl)
If TypeName(ctrl) = "TextBox" Then 'change to whatever you want to find
ctrl.text = ""
Else
End If
Next ctrl
End Sub Rgds
Geoff

Estne volumen in toga, an solum tibi libet me videre?

 

[yumbelie]

fantastic , as always - many thanks