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

[manicss01]

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.

 

[Andrew Poulsom]

Welcome to MrExcel.

You can try:

ActiveSheet.ShowDataForm

 

[manicss01]

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]

Put it in a Sub like this

Sub ShowForm()
    ActiveSheet.ShowDataForm
End Sub

and assign it to a button or shape.

 

[manicss01]

Put it in a Sub like this

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]

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]

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?

 

[Andrew Poulsom]

You need a worksheet with just the table.

 

[manicss01]

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.

 

[Andrew Poulsom]

Why don't you start your table in A1?