DataForm Problem

Andywiz

Board Regular
Joined
May 25, 2005
Messages
238
Hello,

I'm having trouble with something very simple and cannot figure out why!

I'm replicating the Data > Form command using:

Code:
Range("b200").End(xlUp).Select
ActiveSheet.ShowDataForm

However, this fails to open the Data Form correctly, insisting that "Microsoft Excel cannot determine which row in your list or selection contains column labels", and thereby opening a DataForm that does not contain the correct fields for entry.

Normally, I understand this to mean that I don't have any column headers. However, I do, and in fact if I perform this manually by actually clicking Data > Form, it opens the data form correctly without problems.

Can anybody suggest why this might be happening and help me resolve it, please?

Many thanks in advance,
Andywiz :eek:
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
This is probably being caused by having all text values in your second row. (Try overwriting a cell in the second row with a number or date, if that's true and see if this still happens.) This can be overcome by turning off display alerts.
Code:
Range("b200").End(xlUp).Select
Application.DisplayAlerts = False
ActiveSheet.ShowDataForm
Application.DisplayAlerts = True
 

Andywiz

Board Regular
Joined
May 25, 2005
Messages
238
Greg,

Appreciate the suggestion, but it doesn't seem to make any difference. Tried replacing the values and editing the code as you suggested - same result (just now, I don't get the error message before the blank dataform opens).

Any other ideas? Anybody?

Cheers,
Andywiz :confused:
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222
This might also work, regardless of what cell you are on at the time, not even within the dataform recordset range, as long as it you are somewhere on the same worksheet as the data.


Sub Test1()
Application.DisplayAlerts = False
Application.SendKeys "%n"
ActiveSheet.ShowDataForm
Application.DisplayAlerts = True
End Sub
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

Then likely 1 of 2 things.
  1. Either a different sheet than what you think is the active sheet. Or
  2. You have spaces or some other bit of garbage in column B.

As a first step, take a look at Nimrod's suggestion in this thread. There he describes a more robust method of finding the last value than using range.end(xlup) However your problem looks to be almost the reverse of the OP in that thread. You would then want to check to make sure that the cell found is not a space. You can use vb's TRIM function (and perhaps Excel's worksheet function CLEAN()) to effect the testing.

HTH
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Oh - good morning, Tom! Um, not following, what's the alt-N gonna do for us?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

I had a runtime error with the DataForm method if the header wasn't in rows 1 or 2. But this worked:

Code:
Sub Test()
    Range("B200").End(xlUp).Select
    Application.SendKeys "%DO"
End Sub
 

Andywiz

Board Regular
Joined
May 25, 2005
Messages
238
Yes, Andrew, that appears to have been the cause of my problem - the list started on row 5 of the sheet. I didn't realise this was a constraint on the use of dataforms.... d'oh!

With much fiddling I've rearranged my sheet so that the list starts on row 2 now, and the ShowDataForm method works fine now.

Thanks to all of you for your valuable and rapid input - apologies if you feel I've wasted your time with something so simple.

Thanks esp. Greg for trying twice!

Cheers,
Andywiz :)
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Andrew Poulsom said:
I had a runtime error with the DataForm method if the header wasn't in rows 1 or 2...
That's patently ridiculous! Completely true - but still - just plain silly. :biggrin:
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Greg Truby said:
Andrew Poulsom said:
I had a runtime error with the DataForm method if the header wasn't in rows 1 or 2...
That's patently ridiculous! Completely true - but still - just plain silly. :biggrin:

Yup :unsure:

I don't know why, but I started in row 3, and even a recorded macro failed. You come across a new "feature" every day. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,056
Messages
5,569,951
Members
412,299
Latest member
agentless
Top