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:
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
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,273
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,022

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,022
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,022
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. :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,117
Messages
5,857,482
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top