Thread: How do I display data from rows using SpinButton in a form?

How do I display data from rows using SpinButton in a form?

I have a form to add data to a sheet and I am looking for some way to add a Spin Button to "go back" or go to first/last record so I can edit them.

Private Sub CloseForm_Click()
  Unload Me
End Sub


Private Sub Enter_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("RawData")

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

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.PlaFirstName
ws.Cells(iRow, 2).Value = Me.PlaLastName
ws.Cells(iRow, 3).Value = Me.PlaDOB
ws.Cells(iRow, 4).Value = Me.PlaSchool
ws.Cells(iRow, 5).Value = Me.PlaLastYrTeam
ws.Cells(iRow, 6).Value = Me.PlaJersSize
ws.Cells(iRow, 7).Value = Me.PlaPantSize
ws.Cells(iRow, 8).Value = Me.PlaBats
ws.Cells(iRow, 9).Value = Me.PlaPitch
ws.Cells(iRow, 10).Value = Me.PlaPos
ws.Cells(iRow, 11).Value = Me.PlaNewDraft1
ws.Cells(iRow, 11).Value = Me.PlaNewDraft2
ws.Cells(iRow, 12).Value = Me.ParentInfo1FirstName
ws.Cells(iRow, 13).Value = Me.ParentInfo1LastName
ws.Cells(iRow, 14).Value = Me.ParentInfo1Relationship
ws.Cells(iRow, 15).Value = Me.ParentInfo1Street
ws.Cells(iRow, 16).Value = Me.ParentInfo1Apt
ws.Cells(iRow, 17).Value = Me.ParentInfo1City
ws.Cells(iRow, 18).Value = Me.Parent1State
ws.Cells(iRow, 19).Value = Me.ParentInfo1Zip
ws.Cells(iRow, 20).Value = Me.ParentInfo1HomePhone
ws.Cells(iRow, 21).Value = Me.ParentInfo1MobilePhone
ws.Cells(iRow, 22).Value = Me.ParentInfo1Email
ws.Cells(iRow, 23).Value = Me.ParentInfo1CoachYes
ws.Cells(iRow, 23).Value = Me.ParentInfo1CoachNo
ws.Cells(iRow, 24).Value = Me.ParentInfo1StandYes
ws.Cells(iRow, 24).Value = Me.ParentInfo1StandNo
ws.Cells(iRow, 25).Value = Me.ParentInfo1Notes
ws.Cells(iRow, 26).Value = Me.ParentInfo2FirstName
ws.Cells(iRow, 27).Value = Me.ParentInfo2LastName
ws.Cells(iRow, 28).Value = Me.ParentInfo2Relationship
ws.Cells(iRow, 29).Value = Me.ParentInfo2Street
ws.Cells(iRow, 30).Value = Me.ParentInfo2Apt
ws.Cells(iRow, 31).Value = Me.ParentInfo2City
ws.Cells(iRow, 32).Value = Me.ParentInfo2State
ws.Cells(iRow, 33).Value = Me.ParentInfo2Zip
ws.Cells(iRow, 34).Value = Me.ParentInfo2HomePhone
ws.Cells(iRow, 35).Value = Me.ParentInfo2MobilePhone
ws.Cells(iRow, 36).Value = Me.ParentInfo2Email
ws.Cells(iRow, 37).Value = Me.ParentInfo2CoachYes
ws.Cells(iRow, 37).Value = Me.ParentInfo2CoachNo
ws.Cells(iRow, 38).Value = Me.ParentInfo2StandYes
ws.Cells(iRow, 38).Value = Me.ParentInfo2StandNo
ws.Cells(iRow, 39).Value = Me.ParentInfo2Notes

'clear the data
Me.PlaFirstName.Value = ""
Me.PlaLastName.Value = ""
Me.PlaDOB.Value = ""
Me.PlaSchool.Value = ""
Me.PlaLastYrTeam.Value = ""
Me.PlaJersSize.Value = ""
Me.PlaPantSize.Value = ""
Me.PlaBats.Value = ""
Me.PlaPitch.Value = ""
Me.PlaPos.Value = ""
Me.PlaNewDraft1.Value = ""
Me.PlaNewDraft2.Value = ""
Me.ParentInfo1FirstName.Value = ""
Me.ParentInfo1LastName.Value = ""
Me.ParentInfo1Relationship.Value = ""
Me.ParentInfo1Street.Value = ""
Me.ParentInfo1Apt.Value = ""
Me.ParentInfo1City.Value = ""
Me.Parent1State.Value = ""
Me.ParentInfo1Zip.Value = ""
Me.ParentInfo1HomePhone.Value = ""
Me.ParentInfo1MobilePhone.Value = ""
Me.ParentInfo1Email.Value = ""
Me.ParentInfo1CoachYes.Value = ""
Me.ParentInfo1CoachNo.Value = ""
Me.ParentInfo1StandYes.Value = ""
Me.ParentInfo1StandNo.Value = ""
Me.ParentInfo1Notes.Value = ""
Me.ParentInfo2FirstName.Value = ""
Me.ParentInfo2LastName.Value = ""
Me.ParentInfo2Relationship.Value = ""
Me.ParentInfo2Street.Value = ""
Me.ParentInfo2Apt.Value = ""
Me.ParentInfo2City.Value = ""
Me.ParentInfo2State.Value = ""
Me.ParentInfo2Zip.Value = ""
Me.ParentInfo2HomePhone.Value = ""
Me.ParentInfo2MobilePhone.Value = ""
Me.ParentInfo2Email.Value = ""
Me.ParentInfo2CoachYes.Value = ""
Me.ParentInfo2CoachNo.Value = ""
Me.ParentInfo2StandYes.Value = ""
Me.ParentInfo2StandNo.Value = ""
Me.ParentInfo2Notes.Value = ""

Me.PlaFirstName.SetFocus

'clear the data
End Sub



Private Sub SpinButton1_Change()

End Sub

Private Sub UserForm_Click()

End Sub
Private Sub UserForm_Initialize()
PlaJersSize.AddItem "Youth Small"
PlaJersSize.AddItem "Youth Med"
PlaJersSize.AddItem "Youth Large"
PlaJersSize.AddItem "Youth XL"
PlaJersSize.AddItem "Adult Small"
PlaJersSize.AddItem "Adult Med"
PlaJersSize.AddItem "Adult Large"
PlaJersSize.AddItem "Adult XL"
PlaPantSize.AddItem "Youth Small"
PlaPantSize.AddItem "Youth Med"
PlaPantSize.AddItem "Youth Large"
PlaPantSize.AddItem "Youth XL"
PlaPantSize.AddItem "Adult Small"
PlaPantSize.AddItem "Adult Med"
PlaPantSize.AddItem "Adult Large"
PlaPantSize.AddItem "Adult XL"
PlaPantSize.AddItem "Youth Small"
PlaBats.AddItem "Right"
PlaBats.AddItem "Left"
PlaBats.AddItem "Both"
PlaPitch.AddItem "Right"
PlaPitch.AddItem "Left"
PlaPitch.AddItem "Both"
    PlaPos.AddItem "Catcher"
    PlaPos.AddItem "Pitcher"
    PlaPos.AddItem "First Base"
    PlaPos.AddItem "Second Base"
    PlaPos.AddItem "Third Base"
    PlaPos.AddItem "Short Stop"
    PlaPos.AddItem "Left Field"
    PlaPos.AddItem "Center Field"
    PlaPos.AddItem "Right Field"
End Sub
Private Sub Form_Load()
UserForm.Image1 = App.Path & "\cll.png"
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

If there is another way besides the spin button I can use that too.

Thanks,
Mark

Sounds good. I will upload it once I get the dummy workbook completed.