Excel-2007 Macro Enabled File is available to download at the end of page.
When you open this file, it will look like shown in below image.
When you click on button, it will open VBA form which counts last free line and jump on it to insert data.
he Form will look like shown in below image.
To find the coding of this form click on right click on sheet name and select "View Code"
Button is assigned with below macro which then open up the form.
Sub OpenCourseBookingForm()
UserForm1.Show
End Sub
Below is the function which finds the Last Empty Cell for the given sheet name
Private Function Get_Empty_Cell_Index(ByVal SheetName As String) As Long
On Error GoTo Last
Dim Index As Long
Index = 11
ActiveWorkbook.Sheets(SheetName).Activate
Range("A" & Index).Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
Index = Index + 1
End If
Loop Until IsEmpty(ActiveCell) = True
Range("A" & Index).Select
GoTo Last_1
Last:
MsgBox ("Sheet:" & SheetName & ": Cell No:" & Index & " - " & Err.Number & ":" & Err.Description)
Index = -1
Last_1:
Get_Empty_Cell_Index = Index
End Function
while below code is used to count Next Number in sequence for the given sheetname
Private Function Get_NextNo(ByVal SheetName As String) As Long
Dim NextNo As Long
ActiveWorkbook.Sheets(SheetName).Activate
Range("A11").Select
Do
If IsEmpty(ActiveCell) = False Then
NextNo = ActiveCell.Value + 1
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
Get_NextNo = NextNo
End Function
Download File VBA Application File
