Excel Spreadsheet forms...

kevin.philips

Hi,

Just going back to your form and clearing the values within the textboxes, the following should be more efficient:

For each ctl in Userform1.controls
If typename(ctl) = "textbox" then ctl.text = ""
next ctl

This assumes that the form is called UserForm1.

The following may also help and is in addition to Chippy's comments:

Where you have the range of names you want to use, you could have this list act dynamically, in that if you add another name you won't need to redefine the named range

Enter this into the Insert > Name > Define

Select a name for the range and in the refers to enter the following formula taking note of the further notes:

=VarEntry!$A$2:INDIRECT("A"&COUNTA(VarEntry!$A:$A))

VarEntry! Rename to your sheet that contains the list
$A$2 Rename to the starting cell in your list
A Change all other column references to you column name

HTH

Regards

Kevin

robert_harper_2000

Right... I wanted my form to once the information has been added via my finished button to reset and start blank again.

Is this what everyone is trying to help me with ? lol sorry for being an idiot


ps I want the formula =today() but where do I put this?? it needs to be in my form somewhere

I've created a text box and a row etc

Private Sub txtdate_Change()

End Sub

What can I use =today() doesn't work....

kevin.philips

Hi Robert,

I would use a new sub as follows and call it after you have finished writing the data to the sheet:

Private Sub ClearTextBoxes()

For each ctl in Userform1.controls
If typename(ctl) = "textbox" then ctl.text = ""
next ctl

End Sub

So where you have the following line in your click event:

txtpet.SetFocus

Just before it call the new sub as you have done before using:

Call ClearTextBoxes

For the date part you require in your form, you will need to assign the value to a label or other available control (I suggest a label) in the form initialize event, you can't use =Today() from within VB unless you write a User Defined Function and will need to use = Now() to get a full date or =Date() to get a short date (System settings dependant), do a google search for Formatting Dates in VBA or check out =FormatDateTime for a more flexible solution.

HTH

Best Regards

Kevin

robert_harper_2000

Date = sorted lol

Now back to this clearing, I've type as a test but it comes up with the error for every For statement there needs a next?

Private Sub ClearTextBoxes()

For Each ctl In UserForm1.Controls
If txtNumber(ctl) = "textbox" Then ctl.Text = ""
If txtpet(ctl) = "textbox" Then ctl.Text = ""

End Sub

robert_harper_2000

ps for the other call statement I placed it here as you explained

Private Sub cmdAdd_click_Click()

'Declare variable intNext as an integar'
Dim intNext As Integer

'Set the value of intNext to be the number of rows in Dataset'
intNext = Range("Dataset").Rows.Count + 1

'Transfer the values in the form into the spreadsheet'
Range("A" & intNext) = txtNumber.Value
Range("B" & intNext) = txtpet.Value
Range("C" & intNext) = txtqty.Value
Range("D" & intNext) = txtprice.Value
Range("E" & intNext) = txtstaff.Value
Range("F" & intNext) = Txtname.Value
Range("G" & intNext) = txtAddress.Value
Range("H" & intNext) = txtphone.Value
Range("I" & intNext) = txtdate.Value
Range("J" & intNext) = txtcomments.Value

'Runs the nextNumber procedure to display the next available number'
Call NextNumber

'clears txt boxes'
Call ClearTextBoxes

'Move the cursor to txttitle'
txtpet.SetFocus

End Sub

tomstickland

robert_harper_2000 wrote:

Date = sorted lol

Now back to this clearing, I've type as a test but it comes up with the error for every For statement there needs a next?

Private Sub ClearTextBoxes()

For Each ctl In UserForm1.Controls
If txtNumber(ctl) = "textbox" Then ctl.Text = ""
If txtpet(ctl) = "textbox" Then ctl.Text = ""

End Sub

YOu're missing the next statement.

For Each ctl In UserForm1.Controls
If txtNumber(ctl) = "textbox" Then ctl.Text = ""
If txtpet(ctl) = "textbox" Then ctl.Text = ""
Next ctl

Chippy_Minton

Try this:

Private Sub ClearTextBoxes()

For Each ctl In UserForm1.Controls
If TypeName(ctl) = "TextBox" Then ctl.Text = ""
Next

End Sub

robert_harper_2000

assume the file name is txtpet & form is frmcontactinformation


Private Sub ClearTextBoxes()

For Each ctl In frmcontactinformation.Controls
If txtpet(ctl) = "txtpet" Then ctl.Text = ""
Next

End Sub

Is this correct? no lol

With even Private Sub ClearTextBoxes() - I get ambiguous name detected..??

Hold on moved it around and now... just testing

ah ...
If txtNumber(ctl) = "txtnumber" Then

Created error mismatched....
For Each ctl In frmcontactinformation

doesn't know this - is ctl something I should have named?

Chippy_Minton

You're getting the ambiguous name error because you have 2 or more ClearTextBoxes subroutines defined. Rename or delete all except one of them.

Using your form name the subroutine is:

Private Sub ClearTextBoxes()

For Each ctl In frmcontactinformation.Controls
If TypeName(ctl) = "TextBox" Then ctl.Text = ""
Next

End Sub

robert_harper_2000

I do REALLY appreciate this help by the way

It still says mismatch:

Private Sub ClearTextBoxes()

For Each ctl In frmcontactinformation.Controls
If txtNumber(ctl) = "textbox" Then ctl.Text = ""
If txtpet(ctl) = "textbox" Then ctl.Text = ""
If txtqty(ctl) = "textbox" Then ctl.Text = ""
If txtprice(ctl) = "textbox" Then ctl.Text = ""
If txtstaff(ctl) = "textbox" Then ctl.Text = ""
If Txtname(ctl) = "textbox" Then ctl.Text = ""
If txtAddress(ctl) = "textbox" Then ctl.Text = ""
If txtphone(ctl) = "textbox" Then ctl.Text = ""
If txtdate(ctl) = "textbox" Then ctl.Text = ""
Next

End Sub

Highlighting ' If txtNumber(ctl) = "textbox" Then '