DataForm Problem

[Andywiz]

Hello,

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

I'm replicating the Data > Form command using:

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

 

[Greg Truby]

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.

Range("b200").End(xlUp).Select
Application.DisplayAlerts = False
ActiveSheet.ShowDataForm
Application.DisplayAlerts = True

 

[Andywiz]

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

 

[Tom Urtis]

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]

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]

Oh - good morning, Tom! Um, not following, what's the alt-N gonna do for us?

 

[Andrew Poulsom]

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

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

 

[Andywiz]

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]

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.

 

[Andrew Poulsom]

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.

Yup

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.