Automatically put date in column when using a form

[Sue]

I have a spreadsheet that has 6 columns (B through G) that will be populated
using Data, Form to give the input a consistant interface. I want to have
the current date entered automatically in the corresponding cell in Column A
so that I can run reports based on that date. I am using the
Worksheet_Change (ByVal Target As Range) and it works great if I enter the
data directly in the cells, but if I try to use Date, Form to enter the
data, the date is not automatically put into Column A. Is there anyway to
do this? I don't want the user to have to enter a key combination just to
input the date, it needs to be put there automatically. Any help is greatly
appreciated.

Sue

 

[Dave Peterson]

John Walkenbach has an enhanced userform at:
http://j-walk.com/ss/dataform/index.htm

The source code is protected, but for a nominal fee ($20 US), you can modify the
code to do what you want.

As an alternative, maybe you could populate that column A after you finish the
data entry--maybe the first worksheet_selectionchange.

Another option might be to use a macro to show the form. When you close the
form, the macro will continue and it could populate the empty cells in column
A. You shouldn't have to worry about existing data, right???

Option Explicit
Sub testme()
With Worksheets("sheet1")
.Range("b1:i" & .Cells(.Rows.Count, "B").Row).Name = "DataBase"
.ShowDataForm
.Range("database").Resize(, 1).Offset(0, -1) _
.Cells.SpecialCells(xlCellTypeBlanks).Value = Date
.Range("a:a").EntireColumn.AutoFit
End With
End Sub

(The worksheet_change doesn't fire in xl2002 from changes made via Data|form,
either--just in case you might be wondering if upgrading will help--although you
didn't state your verson.)

 

[Dave Peterson]

And just in case you don't have any empty cells in that range:

Option Explicit
Sub testme()
With Worksheets("sheet1")
.Range("b1:i" & .Cells(.Rows.Count, "B").Row).Name = "DataBase"
.ShowDataForm
On Error Resume Next
.Range("database").Resize(, 1).Offset(0, -1) _
.Cells.SpecialCells(xlCellTypeBlanks).Value = Date
On Error GoTo 0
.Range("a:a").EntireColumn.AutoFit
End With
End Sub

 

[Sue]

Thanks for the info, Dave. I like your idea of using a macro to start the
process and after completing the data entry the macro would populate the
date column. The user that I'm designing this for needs this to be as
simple as possible, and I think this gets me there. For what it's worth,
I'm using Excel 2000 and don't know if the office has plans to upgrade
anytime soon so I appreciate your explanation of why the Worksheet_Change
doesn't get me where I want to be.

Thanks again,

Sue