Default Data Form

  • Thread starter Thread starter djlong3000
  • Start date Start date
D

djlong3000

I have a basic worksheet for which I like to use the default data entr
form. In other words it benefits from using a form but is just no
worth a lot of custom form making.

However while I do have an auto_open macro that moves to the first ne
row in the range after all existing records, and then ends o
ShowDataForm. But the default form always opens on the first record i
the list.

Is there a simple way to make the default form open on the row of th
active cell, , instead of at the top of the list ?


Thanks.

Doug Lon
 
Maybe something like this:

Option Explicit
Sub auto_open()

Dim nextRow As Long
With Worksheets("sheet1")
'.Select 'if you want to see the sheet???
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
SendKeys "{DOWN " & nextRow - 2 & "}{TAB 3}"
Application.DisplayAlerts = False
.ShowDataForm
Application.DisplayAlerts = True
End With

End Sub
 
I use the following to have a form open to the next blank field....
SendKeys "%w"
Hope this can help.
Rob
 
And it's probably safer just sending one keystroke than many.

(I stole my code from an example to open on the active row.)
 
Dave / Rob -

Thanks for the help. I tried both of these suggestions and I still
can't get the form to open with anything other than the first record.

Now, on the other hand, I may not have used your suggestions exactly
correctly, too. But I think I did.

Here is what I have as the auto open macro:

ActiveWorkbook.Sheets("Daily").Activate
Range("C2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.ShowDataForm

The sheet has a database of 15 columns, with a header row, and the
first two columns are dates and day of week. So I go to C2 and select
down to the end of the rows that have records, and then down one more
row to the first empty line.

( This then is the point where I inserted the suggested SendKeys"%W",
in case that's not right. I put it on the line before ShowDataForm.
)

When the data form appears, it ignores the active cell location and
just opens with the first record displaying in it. (Row 2 of the
sheet)

Am I missing something here? :confused:

Doug Long
 
Hi Doug,
I have it working like this in a Control Button and it works good for me.

Private Sub CommandButton1_Click()
SendKeys "%w"
Sheet10.ShowDataForm 'The sheet that has the data you want to apply the form
to.
End Sub

I can't quite understand why you're needing to select the next empty cell
as the data form does that anyway.
I think I would just try...

SendKeys "%w"
Sheets("Daily").ShowDataForm
End Sub

Rob
 

Hi, Rob -

thanks again for response (BTW- any idea why I don't get email
notice that there is a response to this post? The option is
checked, and I even subscribed to the thread ... :confused: )

Anyway: I tried both these new suggestions and still no joy.

But interestingly enough, you said:
rob said:
*...
I can't quite understand why you're needing to select the next empty
cell as the data form does that anyway.
*
Click to expand...
That is my problem: I _don't_ get that responnse as the
normal form action. No matter what I do the form opens up on
the first record - the first row under the field headers.

If I run just your macro, for example, not even selecting moving to
a new row with the active cell,etc, it still opens up with the first
record in the form.

This is a daily report log. The first colum is the date, second is
the weekday of that date, and then there are colums for the data
that is logged each day. I want it to go to the first blank record
so the form is opened to the current date.

Also odd, is that there isn't even a shortcut key to go to the last
record in a list. Ctrl PgUp takes you to the top, but Ctrl PgDn
gives you a new record. (A new record is appended after the list
I am working in, which has the dates of all 365 days in Col 1)

And BTW, this is the "default data form" not a custom one. Hope
we're on the same page there. Anyway, I'm stumped!
[/QUOTE]
 
Rob's code worked ok for me, if excel could determine which row had headers.

If it stopped and yelled and said "...can't determine column Labels....", then
the sendkeys was eaten up.

Option Explicit
Sub testme01()
SendKeys "%w"
Application.DisplayAlerts = False
With Worksheets("sheet1")
.Range("a1").CurrentRegion.Name = "database"
.ShowDataForm
End With
Application.DisplayAlerts = True
End Sub

Another problem that could occur is where excel looks for your data to use in
the data|form.

If there's not a range called Database or the table doesn't start in A1:B2, then
the macro will fail.

Here's an article that explains how the problem occurs in VBA (with solution)
http://support.microsoft.com/default.aspx?scid=KB;en-us;q110462
XL: ShowDataForm Method Fails If Data Can't Be Found

Did any of this help?


djlong3000 < said:

Hi, Rob -

thanks again for response (BTW- any idea why I don't get email
notice that there is a response to this post? The option is
checked, and I even subscribed to the thread ... :confused: )

Anyway: I tried both these new suggestions and still no joy.

But interestingly enough, you said:
rob said:
*...
I can't quite understand why you're needing to select the next empty
cell as the data form does that anyway.
*
Click to expand...
That is my problem: I _don't_ get that responnse as the
normal form action. No matter what I do the form opens up on
the first record - the first row under the field headers.

If I run just your macro, for example, not even selecting moving to
a new row with the active cell,etc, it still opens up with the first
record in the form.

This is a daily report log. The first colum is the date, second is
the weekday of that date, and then there are colums for the data
that is logged each day. I want it to go to the first blank record
so the form is opened to the current date.

Also odd, is that there isn't even a shortcut key to go to the last
record in a list. Ctrl PgUp takes you to the top, but Ctrl PgDn
gives you a new record. (A new record is appended after the list
I am working in, which has the dates of all 365 days in Col 1)

And BTW, this is the "default data form" not a custom one. Hope
we're on the same page there. Anyway, I'm stumped!
Click to expand...

[/QUOTE]
 
Fraid this is getting out of my league now, Doug. But I'm sure Dave will be
able to help you further.
Rob
 
Hi guys -

Once again thanks loads for the help.

Dave: yes I ran into that "-What- data range do you want a
a form for, bub?" message, myself. But only if the active cell was
not in close enough proximimty to the list for Excel to be able to
guess. My original macro was avoiding that by landing just one
row below the last record.

Anyway: Yesterday I left Rob's SendKeys in the autoopen macro
by accident, and when I opened it this AM, sure enough I had a
blank form. However, it was appending records _below_ the
whole range that comprises my list.

Hmmm.

Playing around with it, this is what I found:

My sheet had a first column with each date from 1 jan to 31 Dec
in it. The second colum was a formula to show the weekday of
the date to the left. Then the next bunch of colums was where
the daily log data went.

I set it up this way (with a line for each date) because I thought it
was needed in order to maintain the formula in the second column
For some reason, with the sheet set up that way, the form would
always open up with the first record of the list in it, as I said i
the
last post - even with the SendKeys in it, when I stepped through
it in the VB editor.

But today, it decided to open a completely blank form several
rows under the existing list. (!?) So I deleted the blank rows and
closed and opened the book. It now opened under the last row
of the list -but it showed the sedond field (column B) with no
space to enter data - and when I appended the new record the
formula was copied down from the record above! -

So, I deleted all the rows with the pre-entered dates in Column A
and now it works prefectly: the form opens up with a blank, new
record, but my formula copies forward.

Still no idea why it consistently opened up on the first record and
not a new record form before, but that's a different balrog in the
woodpile.

Thanks again for your help. Turns out you had it right all along,
Rob. I just had to help Excel append to an existing list, and
_not_ help it figure out I wanted that formula in each record
by pre-installing the formulas in column B. It's a pretty smart
program, eh?

[now, as a parting request:
I still can't seem to get email noticed that there are new posts on
this thread - even with that option set to on and even with sub
scribing to the thread. In fact, when I went to the User CP, it said
"no new posts to the thread" though you had both posted to it.
Sure would like to know what's up with that.]

But, even if I have to manually check in daily to look for any new
posts, it well worth the effort. You guys are terrific.

Thanks again
 
I've had trouble when excel couldn't figure out which row held the headers for
each column. Sometimes just bolding that row will enable excel to find the
correct row.

And I could confuse excel with formulas that evaluated to "" and converted to
values.

I've never used excelforum.com to post messages. I think you'll have to ask
that at the site (or is that what User CP is??)

If you're going to hang around here for any time, you may want to connect
directly to the MS NewsServers. I find them updated quicker (and easier to
use).

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm



djlong3000 < said:
Hi guys -

Once again thanks loads for the help.

Dave: yes I ran into that "-What- data range do you want a
a form for, bub?" message, myself. But only if the active cell was
not in close enough proximimty to the list for Excel to be able to
guess. My original macro was avoiding that by landing just one
row below the last record.

Anyway: Yesterday I left Rob's SendKeys in the autoopen macro
by accident, and when I opened it this AM, sure enough I had a
blank form. However, it was appending records _below_ the
whole range that comprises my list.

Hmmm.

Playing around with it, this is what I found:

My sheet had a first column with each date from 1 jan to 31 Dec
in it. The second colum was a formula to show the weekday of
the date to the left. Then the next bunch of colums was where
the daily log data went.

I set it up this way (with a line for each date) because I thought it
was needed in order to maintain the formula in the second column
For some reason, with the sheet set up that way, the form would
always open up with the first record of the list in it, as I said in
the
last post - even with the SendKeys in it, when I stepped through
it in the VB editor.

But today, it decided to open a completely blank form several
rows under the existing list. (!?) So I deleted the blank rows and
closed and opened the book. It now opened under the last row
of the list -but it showed the sedond field (column B) with no
space to enter data - and when I appended the new record the
formula was copied down from the record above! -

So, I deleted all the rows with the pre-entered dates in Column A
and now it works prefectly: the form opens up with a blank, new
record, but my formula copies forward.

Still no idea why it consistently opened up on the first record and
not a new record form before, but that's a different balrog in the
woodpile.

Thanks again for your help. Turns out you had it right all along,
Rob. I just had to help Excel append to an existing list, and
_not_ help it figure out I wanted that formula in each record
by pre-installing the formulas in column B. It's a pretty smart
program, eh?

[now, as a parting request:
I still can't seem to get email noticed that there are new posts on
this thread - even with that option set to on and even with sub
scribing to the thread. In fact, when I went to the User CP, it said
"no new posts to the thread" though you had both posted to it.
Sure would like to know what's up with that.]

But, even if I have to manually check in daily to look for any new
posts, it well worth the effort. You guys are terrific.

Thanks again!
Click to expand...
 
Yes, I like it's "simplicity" in operation as long as you don't confuse it.
I'm glad its doing its thing for you.
I've found too, that fiddling with VBA procedures and/or formulas that
sometimes you need to shut and reopen Excel down completely for the changes
to be incorporated correctly. Don't ask me why!
I think, (as Dave has pointed out to me), is that Excel has a memory and
even though it's bigger than ours it can also get confused ;)

Rob


djlong3000 > said:
Hi guys -

Once again thanks loads for the help.

Dave: yes I ran into that "-What- data range do you want a
a form for, bub?" message, myself. But only if the active cell was
not in close enough proximimty to the list for Excel to be able to
guess. My original macro was avoiding that by landing just one
row below the last record.

Anyway: Yesterday I left Rob's SendKeys in the autoopen macro
by accident, and when I opened it this AM, sure enough I had a
blank form. However, it was appending records _below_ the
whole range that comprises my list.

Hmmm.

Playing around with it, this is what I found:

My sheet had a first column with each date from 1 jan to 31 Dec
in it. The second colum was a formula to show the weekday of
the date to the left. Then the next bunch of colums was where
the daily log data went.

I set it up this way (with a line for each date) because I thought it
was needed in order to maintain the formula in the second column
For some reason, with the sheet set up that way, the form would
always open up with the first record of the list in it, as I said in
the
last post - even with the SendKeys in it, when I stepped through
it in the VB editor.

But today, it decided to open a completely blank form several
rows under the existing list. (!?) So I deleted the blank rows and
closed and opened the book. It now opened under the last row
of the list -but it showed the sedond field (column B) with no
space to enter data - and when I appended the new record the
formula was copied down from the record above! -

So, I deleted all the rows with the pre-entered dates in Column A
and now it works prefectly: the form opens up with a blank, new
record, but my formula copies forward.

Still no idea why it consistently opened up on the first record and
not a new record form before, but that's a different balrog in the
woodpile.

Thanks again for your help. Turns out you had it right all along,
Rob. I just had to help Excel append to an existing list, and
_not_ help it figure out I wanted that formula in each record
by pre-installing the formulas in column B. It's a pretty smart
program, eh?

[now, as a parting request:
I still can't seem to get email noticed that there are new posts on
this thread - even with that option set to on and even with sub
scribing to the thread. In fact, when I went to the User CP, it said
"no new posts to the thread" though you had both posted to it.
Sure would like to know what's up with that.]

But, even if I have to manually check in daily to look for any new
posts, it well worth the effort. You guys are terrific.

Thanks again!
Click to expand...
 
Back
Top