Add Data into Excel-2007 With VBA Form

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