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...
Macros and VBA
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