VBA Scripting Help!!

[hnoshea]

New user of VBA and need help!!

I downloaded a template from Contextures.com that contained a Userform for Parts Inventory. I am trying to change the form and datasheet to contain the fields I need to calculate and have somewhere along the line missed something. I can't get the form to operate.

Anyone want to help me with this form? I can send it via email...

Visual copies of code and form attached...

Thanks

Option Explicit

Private Sub cmmAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")

'find first empty row in database
iRow = ws.Cells(Rose.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.vin.Value) = "" Then
Me.vin.SetFocus
MsgBox "Please enter a VIN number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.FirstName.Value
ws.Cells(iRow, 2).Value = Me.LastName.Value
ws.Cells(iRow, 3).Value = Me.EnterCurrentDate.Value
ws.Cells(iRow, 4).Value = Me.StreetAddress.Value
ws.Cells(iRow, 5).Value = Me.City.Value
ws.Cells(iRow, 6).Value = Me.state.Value
ws.Cells(iRow, 7).Value = Me.Zip.Value
ws.Cells(iRow, 8).Value = Me.phone.Value
ws.Cells(iRow, 9).Value = Me.fax.Value
ws.Cells(iRow, 10).Value = Me.email.Value
ws.Cells(iRow, 11).Value = Me.vin.Value
ws.Cells(iRow, 12).Value = Me.make.Value
ws.Cells(iRow, 13).Value = Me.model.Value
ws.Cells(iRow, 14).Value = Me.odometer.Value
ws.Cells(iRow, 15).Value = Me.Engine.Value
ws.Cells(iRow, 16).Value = Me.Serial.Value
ws.Cells(iRow, 17).Value = Me.transmission.Value
ws.Cells(iRow, 18).Value = Me.transserial.Value
ws.Cells(iRow, 19).Value = Me.driveline.Value
ws.Cells(iRow, 20).Value = Me.FanClutch.Value
ws.Cells(iRow, 21).Value = Me.EaseyPedal.Value
ws.Cells(iRow, 22).Value = Me.solo.Value
ws.Cells(iRow, 23).Value = Me.dca.Value
ws.Cells(iRow, 24).Value = Me.nalcool.Value
ws.Cells(iRow, 25).Value = Me.amps.Value
ws.Cells(iRow, 26).Value = Me.batteries.Value
ws.Cells(iRow, 27).Value = Me.cca.Value
ws.Cells(iRow, 28).Value = Me.idle.Value
ws.Cells(iRow, 29).Value = Me.electrical.Value
ws.Cells(iRow, 30).Value = Me.tire1.Value
ws.Cells(iRow, 31).Value = Me.tire2.Value
ws.Cells(iRow, 32).Value = Me.tire3.Value
ws.Cells(iRow, 33).Value = Me.tire4.Value
ws.Cells(iRow, 34).Value = Me.tire5.Value
ws.Cells(iRow, 35).Value = Me.tire6.Value
ws.Cells(iRow, 36).Value = Me.tire7.Value
ws.Cells(iRow, 37).Value = Me.tire8.Value
ws.Cells(iRow, 38).Value = Me.tire9.Value
ws.Cells(iRow, 39).Value = Me.tire10.Value
ws.Cells(iRow, 40).Value = Me.tire11.Value
ws.Cells(iRow, 41).Value = Me.tire12.Value
ws.Cells(iRow, 42).Value = Me.tire13.Value
ws.Cells(iRow, 43).Value = Me.tire14.Value
ws.Cells(iRow, 44).Value = Me.tire15.Value
ws.Cells(iRow, 45).Value = Me.tiresize.Value
ws.Cells(iRow, 46).Value = Me.tiremanu.Value
ws.Cells(iRow, 47).Value = Me.miles.Value
ws.Cells(iRow, 48).Value = Me.mpg.Value
ws.Cells(iRow, 49).Value = Me.axle.Value
ws.Cells(iRow, 50).Value = Me.pm.Value


'clear the data
Me.FirstName.Value = ""
Me.LastName.Value = ""
Me.EnterCurrentDate.Value = ""
Me.StreetAddress.Value = ""
Me.City.Value = ""
Me.state.Value = ""
Me.Zip.Value = ""
Me.phone.Value = ""
Me.fax.Value = ""
Me.email.Value = ""
Me.vin.Value = ""
Me.make.Value = ""
Me.model.Value = ""
Me.odometer.Value = ""
Me.Engine.Value = ""
Me.Serial.Value = ""
Me.transmission.Value = ""
Me.transserial.Value = ""
Me.driveline.Value = ""
Me.FanClutch.Value = ""
Me.EaseyPedal.Value = ""
Me.solo.Value = ""
Me.dca.Value = ""
Me.nalcool.Value = ""
Me.amps.Value = ""
Me.batteries.Value = ""
Me.cca.Value = ""
Me.idle.Value = ""
Me.electrical.Value = ""
Me.tire1.Value = ""
Me.tire2.Value = ""
Me.tire3.Value = ""
Me.tire4.Value = ""
Me.tire5.Value = ""
Me.tire6.Value = ""
Me.tire7.Value = ""
Me.tire8.Value = ""
Me.tire9.Value = ""
Me.tire10.Value = ""
Me.tire11.Value = ""
Me.tire12.Value = ""
Me.tire13.Value = ""
Me.tire14.Value = ""
Me.tire15.Value = ""
Me.tiresize.Value = ""
Me.tiremanu.Value = ""
Me.miles.Value = ""
Me.mpg.Value = ""
Me.axle.Value = ""
Me.pm.Value = ""
Me.vin.SetFocus
End Sub

Private Sub cmdAdd_Click()

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub ComboBox1_Change()

End Sub

Private Sub Label1_Click()

End Sub

Private Sub Label11_Click()

End Sub

Private Sub Label17_Click()

End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub TextBox61_Change()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub

[Simon Lloyd]

Hi in the code you supplied there is no "call" on the userform, somewhere in your code that starts everything off you need the line

Please Login or Register  to view this content.

provided it is called UserForm1.

You dont need all the empty modules, like

Please Login or Register  to view this content.

if you want to show the userform from within its own module then you would use

Please Login or Register  to view this content.

hope this helps,
Regards,
Simon

[hnoshea]

I might need to make myself a little clearer. I created the form and it opens OK and I created a command button to open the form in Excel. my problem is I can't get the fields I created to feed into a second worksheet within the same workbook. I want the "Add this client" command button to enter the information on the form into the second worksheet and then when you choose the "close form" command button, I want it to erase any data entered into the form. Each time you use the "Add this client" command button it enters that information into a new row in the second worksheet.

Any ideas now??

[Charles]

Hi,

This may help I didn't use all of you variables.
This should find an empty row and post the userform data to the sheet
that you need to name where indicated.

Please Login or Register  to view this content.

[hnoshea]

Private Sub cmmAdd_Click()
Application.ScreenUpdating = False
Dim lrow As Long

lrow = Sheets("PartsData").Range("A65336").End(xlUp).Row + 1

Sheets("PartsData").Activate
With Sheets("PartsData")

PartsData is the name of the worksheet that I want to use to fill up the data. NewClientInfo is the page that contains the link to the form. The form is listed as frmParts.

What did i get wrong??

[Simon Lloyd]

Hnoshea, without you actually telling us what is not working, what errors you get and how far the sequence went before causing a problem we will find it difficult to help!

Regards,
Simon

[hnoshea]

Sorry I didn't realize that wasn't clear. The code is supposed to fill in the partsdata worksheet with the data entered into the form when you use the "Add New Client" command button.

I am able to open the form, enter data but when I hit the "Add New Client" command button, the data on the form does not transfer to the partsdata worksheet. The code given to me by Charles was supposed to help me choose the first available row on the partsdata worksheet and enter the data on the form into that row each time the "Add New Client" command button is used.

I don't get any error messages, the data just doesn't transfer over to the partsdata worksheet.

Hope that helps...

[Simon Lloyd]

Hnoshea, all i can imagine is that you have On Error resume Next somewhere because when i ran the code supplied by charles i had an error, all i had to do was remove the ws before ws.Cells(lrow, ......etc the code then worked perfect!

regards,
Simon

[Simon Lloyd]

heres the code i tested and it worked as expected

Please Login or Register  to view this content.

Regards,
Simon

[hnoshea]

I got the code as you said, but it still isn't transferring to the other worksheet when I hit the "Add New Client" command button.

I think I may have the code in the wrong spot... When you double click on the PartsData Excel Object - this is the code that shows. When you double click on the other objects they are blank.

Guess I got myself in over my head

Option Explicit

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim lrow As Long
Dim ws As Worksheet
Set ws = Sheets("Sheet1")

lrow = ws.Range("A65336").End(xlUp).Row + 1

ws.Activate
With ws

Cells(iRow, 1).Value = Me.FirstName.Value
Cells(iRow, 2).Value = Me.LastName.Value
Cells(iRow, 3).Value = Me.EnterCurrentDate.Value
Cells(iRow, 4).Value = Me.StreetAddress.Value
Cells(iRow, 5).Value = Me.City.Value
Cells(iRow, 6).Value = Me.state.Value
Cells(iRow, 7).Value = Me.Zip.Value
Cells(iRow, 8).Value = Me.phone.Value
Cells(iRow, 9).Value = Me.fax.Value
Cells(iRow, 10).Value = Me.email.Value
Cells(iRow, 11).Value = Me.vin.Value
Cells(iRow, 12).Value = Me.make.Value
Cells(iRow, 13).Value = Me.model.Value
Cells(iRow, 14).Value = Me.odometer.Value
Cells(iRow, 15).Value = Me.Engine.Value
Cells(iRow, 16).Value = Me.Serial.Value
Cells(iRow, 17).Value = Me.transmission.Value
Cells(iRow, 18).Value = Me.transserial.Value
Cells(iRow, 19).Value = Me.driveline.Value
Cells(iRow, 20).Value = Me.FanClutch.Value
Cells(iRow, 21).Value = Me.EaseyPedal.Value
Cells(iRow, 22).Value = Me.solo.Value
Cells(iRow, 23).Value = Me.dca.Value
Cells(iRow, 24).Value = Me.nalcool.Value
Cells(iRow, 25).Value = Me.amps.Value
Cells(iRow, 26).Value = Me.batteries.Value
Cells(iRow, 27).Value = Me.cca.Value
Cells(iRow, 28).Value = Me.idle.Value
Cells(iRow, 29).Value = Me.electrical.Value
Cells(iRow, 30).Value = Me.tire1.Value
Cells(iRow, 31).Value = Me.tire2.Value
Cells(iRow, 32).Value = Me.tire3.Value
Cells(iRow, 33).Value = Me.tire4.Value
Cells(iRow, 34).Value = Me.tire5.Value
Cells(iRow, 35).Value = Me.tire6.Value
Cells(iRow, 36).Value = Me.tire7.Value
Cells(iRow, 37).Value = Me.tire8.Value
Cells(iRow, 38).Value = Me.tire9.Value
Cells(iRow, 39).Value = Me.tire10.Value
Cells(iRow, 40).Value = Me.tire11.Value
Cells(iRow, 41).Value = Me.tire12.Value
Cells(iRow, 42).Value = Me.tire13.Value
Cells(iRow, 43).Value = Me.tire14.Value
Cells(iRow, 44).Value = Me.tire15.Value
Cells(iRow, 45).Value = Me.tiresize.Value
Cells(iRow, 46).Value = Me.tiremanu.Value
Cells(iRow, 47).Value = Me.miles.Value
Cells(iRow, 48).Value = Me.mpg.Value
Cells(iRow, 49).Value = Me.axle.Value
Cells(iRow, 50).Value = Me.pm.Value


End Sub
Application.ScreenUpdating = True
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

[Simon Lloyd]

Hnoshea, for the code to work as expected it needs to be in the userform module, unless you have the data on a worksheet!

So thats probably where your fault lies.

the code should work fine then.

regards,
Simon