Today's date in a dialog box

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

When a user opens a excel spreadsheet I need a dialog box to open to ask the
user to fill in the box with today's date and then that information is
transferred into a cell. Can anyone explain how this can be done.

I'm using excel 2003.

Thanks for your help

Paul
 
This code should go in the ThisWorkbook module:


Private Sub Workbook_Open()
Dim X As String
Dim Nextrow As Long

Nextrow = WorksheetFunction.CountA(Range("A:A")) + 1

X = InputBox("Enter today's date here:")
If Not (IsDate(CDate(X))) Then
MsgBox ("You didn't enter a date")
Exit Sub
End If
If X = "" Then
MsgBox "You didn't type anything"
Exit Sub
End If

Cells(Nextrow, 1) = X

End Sub



HTH,
JP
 
If the users are always going to enter today's date, you don't need to ask.

You could put the formula =today() in a cell. That formula will update when the
date changes.

If you wanted a static date, you could use:

Option Explicit
Sub Auto_Open()
with worksheets("sheet1").Range("a1")
.numberformat = "mm/dd/yyyy"
.value = date
end with
End sub

This subroutine goes into a General module--not behind ThisWorkbook and not
behind a worksheet.

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