Excel 2002 Data Forms runtime error 1004

[Charles Osborne]

I made a macro attached to a button on a worksheet to click
Data>Forms and go to a text formatted cell that has a label
on the same page. The cells to the right of this cell also
have labels so that the data form will show a list. When I
click the button to start the macro I get a runtime error
1004 message if Range().Select is anything other than a1 or
a2. Error reads "ShowDataForm method of Worksheet class
failed". If I put the label in cells a1 or a2 it works fine
but gives me a message before allowing me to continue. When
I click OK it lets me continue and works fine. The message
here reads:

"MS Excel cannot determine which row in your list or
selection contains column labels, which are required for
this command.

*If you want the first row of the selection or list used as
labels and not as data, click OK.
*If you selected a subset of cells in error, select a
single cell, and try the command again.
*To create column labels, click Cancel, and enter a text
label at the top of each column of data.
*For information about creating labels that are easy to
detect, click Help."

The situation at the first * above matches my situation, so
I click OK.

The macro code that works here is below:
Sub CountDinners()
'
' CountDinners Macro
' Macro recorded 5/1/2004 by charles osborne
'
'
Range("a2").Select
ActiveSheet.ShowDataForm
End Sub

Any help is appreciated. If you like I can send file.

Thanks!
~~Charles Osborne

 

[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]

Dave,
Thanks for your reply. I did as you said and still have
problems. The error message is:
Runtime error 1004: Reference is not valid.

Here's the VB code:
Sub DinnerCount()
'
' DinnerCount Macro
' Macro recorded 5/1/2004 by charles osborne
'

'
Application.Goto Reference:="Database"
ActiveSheet.ShowDataForm
End Sub

When I debug, the line with "Application.Goto..." is
highlighted. I don't know why is doesn't like this
reference. Any ideas?

~~Charles

 

[Charles Osborne]

Dave,

I also discovered that after attempting to run the macro
the "Database" named range disappeared. It doesn't show up
at all when I go Insert>Name>Define even though the range
shows up in the "Refers to:" box.

Any ideas? Is Database a reserved name?

~~Charles

 

[Charles Osborne]

Oops. My mistake. The range that is showing up in the
"Refers to:" box is not the range of the data for the
Database I want. It is a random cell wherever the cursor
focus is. Sorry.

~~Charles

 

[Tom Ogilvy]

Is the code in a general module or a sheet module. If a sheet module, try
putting it into a general module.

 

[Tom Ogilvy]

Run code like this

Sub MakeName()
With worksheets("Sheet1")
.Range("B8:Z30").Name = "Database"
End with
End Sub

change the sheet name and the range to reflect where your database is
located.