Excel Spreadsheet forms...

Chippy_Minton

No, replace your entire ClearTextBoxes subroutine in post 21 with the one in post 20! Trust me! :rolleyes:

kevin.philips

Hi robert,

Just use the code I supplied - you do not need to rewrite anything as it loops the controls, thus meaning if you add a new textbox you won't need to add anything to the code.

Regards

Kevin

robert_harper_2000

Excellent that does work! I get it now lol

However the code now overwrites anything in the box it doesn't make a new row like it use to.

I wanted it to enter the data in the row then clear the form, after which any new information entered goes into the row below the last.

If you know what I'm trying to say??

Thanks Also it now doesn't add a customer number... not really a big deal but ....

kevin.philips

Hi Robert,

Its probably the order of your code, I made a small workbook to show you which is attached. Equally, please attach your workbook and I'm sure we can help you.Click and save before opening

Best Regards

kevin

robert_harper_2000

erm couldn't find the attach button but copied it and paste ok...
Private Sub NextNumber()

'Calculates and displays the next available contact number'
txtNumber = Range("Dataset").Rows.Count

End Sub

Private Sub ClearTextBoxes()

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

End Sub

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

Private Sub frmcontactinformation_Initialize()

'Runs the NextNumber procedure'
Call NextNumber

End Sub

Private Sub txtname_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Txtname = vbNullString Then Exit Sub
If IsNumeric(Txtname) Then
MsgBox "Sorry, text only"
Txtname = vbNullString
Cancel = True 'Stops them leaving with numbers in the TextBox'
End If
End Sub

Private Sub txtphone_Change()
If txtphone = vbNullString Then Exit Sub
If Not IsNumeric(txtphone) Then
MsgBox "Sorry, numbers only & no spaces"
txtphone = vbNullString
End If
End Sub

Private Sub Txtprice_Change()
If txtprice = vbNullString Then Exit Sub
If Not IsNumeric(txtprice) Then
MsgBox "Sorry, numbers only"
txtprice = vbNullString
End If
End Sub

Private Sub Txtqty_Change()
If txtqty = vbNullString Then Exit Sub
If Not IsNumeric(txtqty) Then
MsgBox "Sorry, numbers only"
txtqty = vbNullString
End If
End Sub

Private Sub UserForm_Initialize()

'Fill the list Box'
With txtstaff
.AddItem "Sarah"
.AddItem "Sammy"
.AddItem "Lisa"
.AddItem "Tasha"
.AddItem "Kirsty"
.AddItem "Dave"
.AddItem "Rob Jnr."
.AddItem "Rob Snr."
End With
'Select the first list item'
txtstaff.ListIndex = 0

'Fill the list Box'
With txtpet
.AddItem "Rodent"
.AddItem "Small Animal"
.AddItem "Bird"
.AddItem "Kitten"
End With
'Select the first list item'
txtpet.ListIndex = 0

txtdate.Text = Date

End Sub


Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

End Sub

robert_harper_2000

http://www.uploading.com/files/19ZNUXLM/Animal_Book.xls.html


dunno if this will work but tried this site that helps ppl share!

kevin.philips

Mm,

Your workbook works for me...!!!!!

It just doesn't add the id but this should do the trick

Range("A" & intNext) = intNext

Instead of assigning it to a text box which in your case helps as if it isn't added then the row count will be wrong.

Regards

Kevin

robert_harper_2000

WOW WOW WOW SUCCESS!!!!

THANK YOU THANK YOU

One other thing hehehe don't worry if I'm taking the biscuit you have done more than enough but if you see my second button.... this was for if the customer was buying a bird and a kitten ( so the quantity would still be one ) I wanted it to keep the details of the name and address but just add on row below an extra animal with the customer details being only typed once..

What do you think? too much work?

Thanks again

robert_harper_2000

I'm sorry I went to far. Listen Thank you so much - all the best. I'll be tarting it up with bits of code I can find.

Thank you

Take care

kevin.philips

Hey Robert,

This is the place for getting help, you wasn't going to far, just that I am at work and can't be looking at the board all day (75% is okay though), I'll have a look and post back

Regards

Kevin