excel macro runtime error 1004

[charles osborne]

I made a macro that clicks a button on one sheet to go to
another sheet, then clicks on a cell, then clicks Data>Form.

After making the macro, when I click the button on sheet
one, the macro takes me to sheet two and then gives me an
error message.

The error I get is:
1"Runtime error 1004
ShowDataForm method of Worksheet class failed."

When I run the debug 3 lines show with the last line being
highlighted. It reads: ActiveSheet.ShowData Form

Any help is appreciated.

~~Charles Osborne

 

[Frank Kabel]

Hi
as always post the relevant part of your code

 

[Dave Peterson]

http://support.microsoft.com/default.aspx?scid=KB;en-us;q110462
XL: ShowDataForm Method Fails If Data Can't Be Found

Describes the problem.

Excel looks in a range named DataBase, then the range A1:B2. If it can't find
it, you get the error.

So the easiest workaround is to name your range DataBase.

Maybe even in the VBA code:

.....
Range("e19").currentregion.name = "database"
activesheet.showdataform
....

depending on where your database is located.

 

[Charles Osborne]

Ok. Here's the VB code for the macro.
Sub OrderEntry()
'
' OrderEntry Macro
' Macro recorded 5/1/2004 by charles osborne
'

'
Sheets("Order Entry").Select
Range("B30").Select
ActiveSheet.ShowDataForm
End Sub

 

[Frank Kabel]

Hi
if your database starts in the first row try using the following code:
Sub OrderEntry()
Sheets("Order Entry").Select
Range("A1").Select
ActiveSheet.ShowDataForm
End Sub

 

[Dave Peterson]

One more way:

Sub OrderEntry()
Sheets("Order Entry").Select
Range("B30").currentregion.name = "database"
ActiveSheet.ShowDataForm
End Sub

This assumes that the current region defines your database.

 

[Charles Osborne]

Frank,
Thanks, but originally the database started in a lower row
with headers above it. I still haven't been able to get it
to work like I wanted, so I'm doing a workaround. I put the
database at a1 and put my headings around aa1. I built the
macro to hide the database when it runs the Data>Form since
the database would show during the form entry. On form
close the macro unhides the database. Not as nice as I
wanted, but it will work.

Next issue: When I protect the worksheet the macro quits
with another error related to the hide function. For now I
will not protect the worksheet.

~~Charles

 

[Frank Kabel]

Hi Charles
you can unprotect and protect the worksheet within your macro

 

[AlfD]

Hi!

I used to have this problem...

I think it was Debra Dalgleish who pointed out that the database o
which the data form is predicated has to start in the area A1:B2

Try it : it solved my problem!

Al