Excel VBA question about form fields?

Question

I have created a form which will input data into an Excel worksheet. The form uses text boxes, check box and option buttons to allow the user to input data.

Once the form is filled I want the user to be able to press a button which will submit the data and then clear the form fields. I have finished creating the form and it works fine.

Is there an easy line of code which will clear all the form fields using the 'submit' button?

Answer 1

Normally when you write code, it is a good thing to name stuff with memorable names for ease in programming later to know what refers to what. But the easiest way to do something like that is to pretty much name them all the same then loop through them.



Excel VBA makes it quite a bit harder than with regular VB programming. In VB programming you could just set up an array of objects. By that I mean you could name the objects TextBox(1), TextBox(2), TextBox(3), ect. Then you could do something like this:



Sub CommanButton1_Click



Dim x As Integer



For x = 1 to 10



TextBox(x).Value = ""



Next x



End Sub





You can't really do that with VBA unfortunately. VBA does not like arrays of Objects. If you did it in VBA, you'd have to use Set to set every object to a declared object variable you declare and that isn't really kind of worth the time or effort.



The best way I've found to go about it is to name them all just about the same, just about like Excel does when you add a new textbox. For example, it starts TextBox1, TextBox2, TextBox3, etc.



If you have ten text boxes, name them for example TextBox1 all the way through TextBox10. Make sure you don't skip a number. Then you can write some code like this:



Sub CommandButton1_Click



Dim x As Integer



For x = 1 To 10



Me.Controls("TextBox" & x).Value = ""



Next x



End Sub



Otherwise you'd have to write code for every TextBox to clear them.

Answer 2

Submit + Ctrl+Alt+Delete, have you looked Excel VBA on the microsoft.com site?

Answer 3

'im not sure what do u recommend via "whilst somebody inputs documents right into a surname field contained in the kind", plz clarify extra? (e.g. which journey?) if surname.text cloth=vbnullstring then msgbox "you will desire to enter the call",vbExclamation else 'if surname isn't clean,the code right here would be achieved end if