Auto fill Date

[imageres]

Is there a way to have the current date input to an invoice that I hav
created in Excel as soon as I oupen the form?

Thanks Dou

 

[Earl Kiosterud]

Doug,

I think by "open the form" you mean the workbook or template. The following
code in module ThisWorkbook will do it:

Private Sub Workbook_Open()
If Sheets("Sheet1").Range("A1") = "" Then Sheets("Sheet1").Range("A1") = Now
If Range("InvoiceDate") = "" Then Range("InvoiceDate") = Now

End Sub

Use one line or the other. The second is preferable, in case you move the
date cell (otherwise the macro won't follow the move), though you must
define (Insert - Name - Define) the name "InvoiceDate" for that cell.

 

[Btibert]

While that certainley is a more elegant way of solving the problem, her
is a simple way to work through the code (as I am begginning and assum
you know at least as much as I do).


------------
Sub Workbook_Open()
Range("A1") = Now()
Range("a1").NumberFormat = "mmmm d, yyyy"
End Sub
------------

The only thing you need to do is Change "A1" both times to the cel
that you need the date to be in, whether it is D3, F7, etc. The forma
is such that the date appears as June 3, 2004.

As I said, its a crude way of solving the problem, but at least its
general idea.

Hope this helps,

Broc

 

[imageres]

This looks like what I am trying to do. However, I am having troubl
with the syntax. Can you give me a little more detail as to just wha
and were I place the code you are giving me? My work book is calle
"Masterlanscape"; the Sheet is called "Estimate" and the cell is name
"EstimateDate"

Thanks Doug:confused

 

[David McRitchie]

Hi Doug,
replace Sheet1 with Estimate
The workbook name does not matter since it only applies to one workbook.
If you followed the directions for creating a named range
you should be able to put the name InvoiceDate into the name box
to the left of the address bar and you should be switched to your
Estimate worksheet.

I would use Date instead of Now. Since Date is just the date
and Now is the current Date and Time.

To install the macro right click on the icon to the left of the
menu bar. View Code, insert your code. You now have macros.
Tools, macros, security, Medium (you want medium not high as your setting).

You can read more about Event macros on my event.htm webpage.

 

[Earl Kiosterud]

Brock,

Yours and mine are essentially the same. I tested for an empty cell (If
Sheets("Sheet1").Range("A1") = "" Then...) because the date shouldn't change
when the file is opened again (where there'd already be a date there) --
that would yield a "date last opened" instead of an invoice date. I also
specified the sheet, in case there's another sheet, and it was left selected
when the file was last saved. In that case, the macro would operate on cell
A1 of that sheet.

When you define a name, it specifies the sheet, as well as the cell, so the
sheet spec isn't needed in the second case. And using a name works if the
cell gets moved (as the name definition gets updated to follow the move).
That's the best way to refer to cells in macros.

 

[Dave Peterson]

Sub auto_open()
worksheets("estimate").range("estimatedate").value = date
end sub

Put this in a general module. Auto_open will run each time you open the
workbook.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Alternatively, you could use the ThisWorkbook module:

Option Explicit
Private Sub Workbook_Open()
worksheets("estimate").range("estimatedate").value = date
End Sub

 

[Earl Kiosterud]

Doug,

In Excel, press Alt-F11. This will open/switch you to the the VBE (Visual
Basic Environment). In the Project Explorer (View - Project Explorer),
locate your workbook (easier if other workbooks aren't open right now). In
that, open a window for module ThisWorkbook by double-clicking it. Paste
the entire macro (below) into it from the email, and remove any line breaks
that the email may have put in (should be three lines). Or type it in. To
run the macro, put the cursor anywhere in it, press F5. Switch back to
Excel (Alt-F11). Check the results. Save the file. Alt-F11 back to the
VBE any time you want, and back. Get a drink. You may need one by now, but
I hope it's working.

Private Sub Workbook_Open()
If Range("EstimateDate") = "" Then Range("EstimateDate") = Now
End Sub

 

[imageres]

Thanks for your help on this one it worked great. However, I now hav
another related question. If I want to use the date in a number o
cells on various sheets how do I do that? Re range names and macr
modification.

Thanks again Dou