Add "Open Form" to existing macro

[cookeetree]

G'day Excel Gods,

I have an existing macro that creates a blank line at the top of my data table. I've just added a data entry form and want this to open when the macro runs. How do I get this to happen? I've tried adding "ActiveSheet.ShowDataForm" to the end of my existing macro, but this errors and I get this:

Run-time error '1004':
ShowDataForm method of Worksheet class failed

How can I make this work? Alternatively, how can I get my form to create the new line at the top of the sheet instead of the bottom???

Any help you could provide would be greatly appreciated.

Cheers, Jason.

 

[DanteAmor]

I need to go through all the code in the userform to see which line of all the code has the problem.

Can you share your file?

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[cookeetree]

I need to go through all the code in the userform to see which line of all the code has the problem.

Can you share your file?

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

I can't share the file, but here's the macro. Everything but the last line is what I was using to create a new line at the top. The last line was added to open the form, but this is where the wheels fall off...

Sub New_Lab_Sample_Form()
'
' New_Lab_Sample_Form Macro
'

'
Range("A3:M3").Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Selection.ClearContents
Range("B3").Select
ActiveCell.FormulaR1C1 = "=R[1]C+1"
Range("A3").Select
ActiveSheet.ShowDataForm

End Sub

 

[DanteAmor]

Sub New_Lab_Sample_Form()
' New_Lab_Sample_Form Macro
Range("A3:M3").Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Selection.ClearContents
Range("B3").Select
ActiveCell.FormulaR1C1 = "=R[1]C+1"
Range("A3").Select
ActiveSheet.ShowDataForm
End Sub

Works for me.
In row 2 from A2 to M2 you must put the headings, preferably with capital letters, so that Excel identifies it as a form.

 

[cookeetree]

Works for me.
In row 2 from A2 to M2 you must put the headings, preferably with capital letters, so that Excel identifies it as a form.

Row 2 already has this.

I can run the macro without the "open form" line and then manually open the form and this works fine. It's only when I try to open the form via the macro that it fails.