VBA coding assistance.

[johnemery]

Hi Guys,

I have created an excel form to load on to a worksheet I have. I am having trouble with the code. I have checked it many times and I may be missing something simple.


I have attached the code the line which is causing an issue is the 2nd part which begins 'find first empty row in the database

Private Sub Cmdbutton_data_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Mastersheet")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1[/B]


'check for a Name number
If Trim(Me.frm_pupil.Value) = "" Then
Me.frm_pupil.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.frm_pupil.Value
ws.Cells(iRow, 2).Value = Me.frm_time.Value
ws.Cells(iRow, 3).Value = Me.frm_staff.Value
ws.Cells(iRow, 4).Value = Me.frm_date.Value
ws.Cells(iRow, 5).Value = Me.frm_intreason.Value
ws.Cells(iRow, 6).Value = Me.frm_eventsprior.Value
ws.Cells(iRow, 7).Value = Me.frm_behaviour.Value
ws.Cells(iRow, 8).Value = Me.frm_routine.Value
ws.Cells(iRow, 9).Value = Me.frm_risk.Value
ws.Cells(iRow, 10).Value = Me.frm_bestaction.Value
ws.Cells(iRow, 11).Value = Me.frm_restrainttype.Value
ws.Cells(iRow, 12).Value = Me.frm_bestaction.Value
ws.Cells(iRow, 13).Value = Me.frm_post.Value

MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.frm_pupil.Value = ""
Me.frm_time.Value = ""
Me.frm_staff.Value = ""
Me.frm_date.Value = ""
Me.frm_intreason.Value = ""
Me.frm_eventsprior.Value = ""
Me.frm_behaviour.Value = ""
Me.frm_routine.Value = ""
Me.frm_risk.Value = ""
Me.frm_bestaction.Value = ""
Me.frm_restrainttype.Value = ""
Me.frm_bestaction.Value = ""
Me.frm_post.Value = ""
Me.frm_pupil.SetFocus
End Sub

Private Sub cmdbutton_cancel_Click()
Unload Me
End Sub


Private Sub frm_pupil_Change()

End Sub

If anyone is able to give me any pointers I would appreciate it?

Thanks

John

[ImranBhatti]

Please Login or Register  to view this content.