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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,302
Messages
5,836,512
Members
430,436
Latest member
fefenouil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top