How to add a button/function that allows you to add a row of data to a table?

manicss01

New Member
Joined
Sep 11, 2014
Messages
9
Hi all,

I'm not the most adept excel user so please bare with me.

I have created a table on excel and want to add a function to the worksheet that allows you to create a new row of data for that table.

I know you can do this using the data entry form but I was wondering whether there was a way to launch that form using a button on the spreadsheet?

All replies will be greatly appreciated.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

manicss01

New Member
Joined
Sep 11, 2014
Messages
9
Where and how do I insert this? Alt+F11 to go into VB, but do I literally just paste this into the empty box and save?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Put it in a Sub like this

Code:
Sub ShowForm()
    ActiveSheet.ShowDataForm
End Sub

and assign it to a button or shape.
 

manicss01

New Member
Joined
Sep 11, 2014
Messages
9

ADVERTISEMENT

Put it in a Sub like this

Code:
Sub ShowForm()
    ActiveSheet.ShowDataForm
End Sub

and assign it to a button or shape.

I have tried doing this and running the macro but it isn't working.

A dialogue box pops up when trying to run the macro reading 'Microsft Excel cannot determine which row in your list or selection contains column labels, which are required for this command'

I press okay and I get another error - runtime error 1004, you can't use this command on a table or range that contains merged cells.

Could I upload the offending file and see if somebody could help me add this?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can put your workbook on a share like Box.com and post the URL if you like. Merged cells are a No-No though.
 

manicss01

New Member
Joined
Sep 11, 2014
Messages
9

ADVERTISEMENT

You can put your workbook on a share like Box.com and post the URL if you like. Merged cells are a No-No though.


I dont think I have access to such sites from work.

There are no merged cells, which is what is confusing me? The only merged cells are above the table (the header of the doc). Do I have to remove them too?
 

manicss01

New Member
Joined
Sep 11, 2014
Messages
9
I have since removed the merged cells, and tried recording the macro once more. I have pressed 'record macro' and carried out the procedure needed to add a new data row to the table. I am now getting the error "Run-time error '1004':
ShowDataForm method of Worksheet class failed"

Please can somebody elucidate?

My table begins on A6 and finishes on H77 (however, it is a dynamic table in the sense that as new rows are added, the table will end lower down).

I really appreciate your help on this.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,233
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top