excel macro runtime error 1004

  • Thread starter charles osborne
  • Start date

C

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
 
Ad

Advertisements

D

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.
 
C

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
 
F

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
 
D

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.
 
C

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
 
F

Frank Kabel

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

Advertisements

A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top