Data entry form help!

Copper Contributor


I have a spreadsheet with 2 sheets.....Sheet A (data) and Sheet B (input). I have created a table in A and managed to create a data entry form.  In sheet B I created a button and attached a macro which opens A and the data entry form.  

The first problem I have is that whenever I try to enter data I get "Cannot extend table or database".

Now this is my wishlist...please note that I am not fluent in VBA etc.

1. Can I create a data entry form that is always displayed in B to enable creation of new data and searching?

2. How do I get rid of the error I am currently getting?

I was hoping to attach a sample but can't see where to do that.

Many thanks





2 Replies


It seems like you are trying to create a data entry form in Excel 365 that allows you to input data into a table and perform searches. To address your questions:

  1. Create a Data Entry Form in Sheet B: To create a data entry form in Sheet B, you can use Excel UserForms, which allow you to design custom data input forms. Here is a simplified guide to creating a basic data entry form:

Step 1: Create a UserForm

  1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
  2. In the VBA editor, go to "Insert" > "UserForm" to create a new UserForm.
  3. Add controls (e.g., text boxes, labels, buttons) to the UserForm to design your data entry form.

Step 2: Code the UserForm

Next, you will need to write VBA code to handle interactions with the UserForm. For example, you will write code to add data to your table when the user clicks a "Submit" button.

Here is a simple example of VBA code to add data to a table:

vba code:


Private Sub SubmitButton_Click()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim newRow As ListRow
    ' Set the worksheet and table
    Set ws = ThisWorkbook.Sheets("SheetA")
    Set tbl = ws.ListObjects("TableName") ' Replace "TableName" with your table name
    ' Add a new row to the table
    Set newRow = tbl.ListRows.Add(AlwaysInsert:=True)
    ' Fill in data from UserForm controls
    newRow.Range(1, 1).Value = TextBox1.Value ' Replace with your control names and values
    ' Close the UserForm
    Unload Me
End Sub


Step 3: Show the UserForm

You can add a button to Sheet B and attach code to display the UserForm when clicked. Here's a simplified example:

vba code:


Private Sub ShowFormButton_Click()
    UserForm1.Show ' Replace "UserForm1" with your UserForm's name
End Sub


  1. Resolve the "Cannot Extend Table or Database" Error:

The "Cannot extend table or database" error typically occurs when you are trying to add data to a table, and Excel is unable to determine the table's size correctly. You can try the following steps to resolve this issue:

  • Ensure that your table has headers.
  • Check if there are any merged cells, blank rows, or columns within your table. These can interfere with the table's structure.
  • Verify that there are no empty rows below the table in Sheet A.
  • If you are using a table formula for column calculations, ensure that it is correctly configured.
  • If the issue persists, try recreating the table in Sheet A to ensure its properly defined.

By creating a UserForm for data entry in Sheet B and handling data submission through VBA code, you can achieve your goal of entering and managing data more effectively. The text, steps and codes were created with the help of AI.


My answers are voluntary and without guarantee!


Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.


Thanks so much....I am amazed that I have got this far so far....still a way to go.

I have created the user form: userform.png

I tried applying the code to 'submit' and 'new entry' as you can see i have a problem with the latter.


I appreciate your patience (this is all new to me) but what have i done wrong to get the error and how do i test the user form.

I am not sure how to open the excel spreadsheet with the user form displayed. 


Code for the 'search' would be great :smile: