First time VBA Userform

[Kryptoroxx]

I am working to create a Userform that enters information as a new entry in a table and the data from the userform fills specific cells in the table. I apologize as I am at work and cannot do the vbb with the mini sheet but I do have pictures and the vba code as I have gotten it to work. I'm still seemingly having an issue where it just overwrites the first line but I think that is because it is searching for the first empty trip number column and with nothing in it it won't work.

Below is the Master Log as I have named it.

Below is about as far as I have gotten using the VBA UserForm and where I am lost simultaneously.

I have gotten the command button to show up and it will bring up the Userform where I can input the information and add a new line to the table. Here's where the fun begins. The trip number I want to be an auto-generated number (no rhyme or reason, just an integer) one greater than the largest number in the table. However this does not have to be a unique identifier number (explanation below)

Private Sub Cancel_Click()

Unload Me

End Sub

Private Sub Clear_Click()

'Empty Account
Account.Value = ""

'Empty DateTime
DateTime.Value = ""

'Empty Destination
Destination.Value = ""

'Empty Pickup
Pickup.Value = ""

'Empty RequestDept
RequestDept.Value = ""

'Empty ReturnDate
ReturnDate.Value = ""

'Empty VehicleType
VehicleType.Value = ""


End Sub

Private Sub OK_Click()

Dim emptyRow As Long

'Make MasterLog active
Sheet1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 3).Value = DateTime.Value
Cells(emptyRow, 5).Value = RequestDept.Value
Cells(emptyRow, 6).Value = Pickup.Value
Cells(emptyRow, 7).Value = Destination.Value
Cells(emptyRow, 8).Value = ReturnDate.Value
Cells(emptyRow, 9).Value = VehicleType.Value
Cells(emptyRow, 12).Value = Account.Value
Cells(emptyRow, 1).Value = TripNumber.Value



End Sub


Private Sub UserForm_Initialize()

'Empty Account
Account.Value = ""

'Empty DateTime
DateTime.Value = ""

'Empty Destination
Destination.Value = ""

'Empty Pickup
Pickup.Value = ""

'Empty RequestDept
RequestDept.Value = ""

'Empty ReturnDate
ReturnDate.Value = ""

'Empty VehicleType
VehicleType.Value = ""

'Add New Trip Number ???
      

End Sub

Even more fun starts to come into play with multi-day trips. So I imagine there is a way to create multiple-entries on the Master log for each 24 hr period (7 days a week) that carry the same trip number. If they must be different I can work with that but I'm trying to learn and then make this extremely useful for myself as the user.

 

[Kryptoroxx]

Scrapped that code. Trying other ways to accomplish this as I cannot get the new row added to the table.

 

[Logit]

Here is the basic code in a User Form. Note the comment ... the fields can be expanded as many times as needed.

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub

Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
    
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub

Download : Simple Database.xlsm