Forum Discussion

porcupine_racer's avatar
porcupine_racer
Copper Contributor
Apr 27, 2023

I'm having trouble with my VBA code for an entry form to a table

Hi! I'm struggling with some bugs that I can't figure out in my VBA code. I've created an entry form and want it to add the data entered into the next available line of a table called "Contacts" in a worksheet called "CONTACTS_DATA". I'm also struggling to find a way to auto-number the the first table column, "Patient ID", when a new record is added. I'm not exactly sure where to put it in the code. I'm not well-versed in coding, so I grabbed the code and attempted to modify it. I can get it to work outside of my table, but I have big plans for the table. Appreciate any help/insight y'all can provide. Thank you in advance!

 

Here is my code:

Private Sub save_button_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("CONTACTS_DATA").ListObject("Contacts")

'find first empty row in database
iRow = ws.Cells(ws.Rows.Count, "Patient ID").End(xlUp).Row + 1

'check for a first name
If Trim(Me.FirstName.Value) = "" Then
Me.FirstName.SetFocus
MsgBox "Please enter a first name"
Exit Sub
End If

'check for a last name
If Trim(Me.LastName.Value) = "" Then
Me.LastName.SetFocus
MsgBox "Please enter a last name"
Exit Sub
End If

'check for an entry user
If Trim(Me.InputBy.Value) = "" Then
Me.InputBy.SetFocus
MsgBox "Please enter YOUR name"
Exit Sub
End If

'check for an entry date
If Trim(Me.InputDate.Value) = "" Then
Me.InputDate.SetFocus
MsgBox "Please enter today's date"
Exit Sub
End If

'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
' .Unprotect Password:="password"
.Cells(iRow, 2).Value = Me.FirstName.Value
.Cells(iRow, 3).Value = Me.MiddleInitial.Value
.Cells(iRow, 4).Value = Me.LastName.Value
.Cells(iRow, 6).Value = Me.EmailAddress.Value
.Cells(iRow, 7).Value = Me.PhoneNumber.Value
.Cells(iRow, 8).Value = Me.StreetAddress.Value
.Cells(iRow, 9).Value = Me.CityAddress.Value
.Cells(iRow, 10).Value = Me.StateAddress.Value
.Cells(iRow, 11).Value = Me.ZipCode.Value
.Cells(iRow, 12).Value = Me.ResponsiblePartyName.Value
.Cells(iRow, 13).Value = Me.ResponsiblePartyRelationship.Value
.Cells(iRow, 14).Value = Me.ResponsiblePartyEmail.Value
.Cells(iRow, 15).Value = Me.ResponsiblePartyPhone.Value
.Cells(iRow, 16).Value = Me.TotalVisitsAuthorized.Value
.Cells(iRow, 20).Value = Me.InputBy.Value
.Cells(iRow, 21).Value = Me.InputDate.Value
' .Protect Password:="password"
End With

'clear the data
Me.FirstName.Value = ""
Me.MiddleInitial.Value = ""
Me.LastName.Value = ""
Me.EmailAddress.Value = ""
Me.PhoneNumber.Value = ""
Me.StreetAddress.Value = ""
Me.CityAddress.Value = ""
Me.StateAddress.Value = ""
Me.ZipCode.Value = ""
Me.ResponsiblePartyName.Value = ""
Me.ResponsiblePartyRelationship.Value = ""
Me.ResponsiblePartyEmail.Value = ""
Me.ResponsiblePartyPhone.Value = ""
Me.TotalVisitsAuthorized.Value = ""
Me.InputBy.Value = ""
Me.InputDate.Value = ""
Me.FirstName.SetFocus

End Sub

Macros and VBA

1 Reply

  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor
    May 07, 2023

    porcupine_racer 

    For a start, you're using the wrong objects. "ws" was intended to be a Worksheet object, but you are assigning a ListObject to it.


    And Excel table manipulation is different. For example, you don't determine what the last row is; just use:

        Dim objNeRow As ListRow
    Set objNewRow = <your_table_variable>.ListRows.Add

    (There is an option with the Add method if the row needs to be created other than at the end.)


    For more information on how to use VBA to add a row to an Excel table, see the attached file (my example code), and (e.g.) this article: Excel VBA - How to Add Rows and Columns to Excel Table with VBA Macro 


    Better coding: There's too much code in the button Click event handler. Separate out the validation code and the data storage code into two new procedures that are called from the event handler.  Again, see the attached file for an example.


    To derive the next PatientID value, you can use this code (with your ListObject object that refers to the Excel table):

        Dim in4PatientID   As Long
    in4PatientID = Application.WorksheetFunction.Max( _
            <your_table_object>.ListColumns("PatientID").DataBodyRange)
    in4PatientID = in4PatientID + 1


    Validation of course needs to be more sophisticated. (E.g., a first name or last name should be rejected if it consists of a period, or if it contains special characters.) And the trimmed (and otherwise edited, e.g., by replacing multiple spaces with a single space) values should be stored back into the input TextBoxes during validation.


    Why would you prompt the user to enter today's date? Instead, use the VBA function Today() to get that value.


    Eventually, add a validation check to see if a record already exists for the patient. (Let the user decide if the existing patient record applies, or if there happen to be two or more patients with the same name.)

     

    (I wanted to send the exported VBA source from my spreadsheet, but could not send it as either .frm or .txt files.  That's why it's a Word document.)

Resources