I have a spreadsheet that requires the user to enter a date on a hidden tab.
The date is used for calculations throughout the spreadsheet.

On opening the sheet, I would like to have some sort of reminder/prompt to
update the date and if possible do the entry without unhiding the sheet.

Are we talking about only one date on the hidden sheet that the user is
prompted to update each time the workbook is opened?

I'm assuming that it's not the current date that is being entered because
you could accomplish that with =TODAY()

Francis Hayes (The Excel Addict)
www.TheExcelAddict.com

"SixBowls" wrote:

I have a spreadsheet that requires the user to enter a date on a hidden tab.
The date is used for calculations throughout the spreadsheet.

On opening the sheet, I would like to have some sort of reminder/prompt to
update the date and if possible do the entry without unhiding the sheet.

Yes. It is one date (a pay period ending). The user opens the sheet for the
prior pay period, updates the date and saves the sheet to a new name.

Ex. User opens file Payroll10-23-09.xls, updates the date on hidden tab to
10/30/2009, updates employee earnings and hours, saves to new file named
Payroll10-30-09.xls.

I need save the spreadsheet for each pay period. If I use TODAY(), when I
open the older spreadsheets the data changes.

"Francis" wrote:

Are we talking about only one date on the hidden sheet that the user is
prompted to update each time the workbook is opened?

I'm assuming that it's not the current date that is being entered because
you could accomplish that with =TODAY()

Francis Hayes (The Excel Addict)
www.TheExcelAddict.com

"SixBowls" wrote:

I have a spreadsheet that requires the user to enter a date on a hidden tab.
The date is used for calculations throughout the spreadsheet.

On opening the sheet, I would like to have some sort of reminder/prompt to
update the date and if possible do the entry without unhiding the sheet.

In Thisworkbook module.

Private Sub Workbook_Open()
filldate = InputBox("Enter a date")
Sheets("hidden").Range("A1") = filldate
End Sub

Substitite your hidden sheet name to suit.


Gord Dibben MS Excel MVP

On Tue, 3 Nov 2009 08:36:01 -0800, SixBowls
wrote:

I have a spreadsheet that requires the user to enter a date on a hidden tab.
The date is used for calculations throughout the spreadsheet.

On opening the sheet, I would like to have some sort of reminder/prompt to
update the date and if possible do the entry without unhiding the sheet.

The following event code displays an inputbox with a prompt when the workbook
is opened, and won't go away (unless you stop the macro by pressing
Ctrl-Break) until a valid date is entered. The date is then entered in a cell
on the hidden sheet (in this example, cell D10 on Sheet3). The current date
is supplied as a default.

Option Explicit

Private Sub Workbook_Open()
Dim NewDate
Do While Not IsDate(NewDate)
NewDate = InputBox("Please enter a date", "Input required", _
Format(Now(), "mm/dd/yyyy"))
Loop
Sheets("Sheet3").Range("D10").Value = NewDate
End Sub

Paste this code in the ThisWorkbook module of the workbook. If you are new
to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/200...e-elses-macro/

Hope this helps,

Hutch

"SixBowls" wrote:

I have a spreadsheet that requires the user to enter a date on a hidden tab.
The date is used for calculations throughout the spreadsheet.

On opening the sheet, I would like to have some sort of reminder/prompt to
update the date and if possible do the entry without unhiding the sheet.

I am not familar with macros but I have pasted them into workbooks in the
past. I read the link with no luck. When I paste to the VBE and run it
updates the date but when I close the book and reopen nothing happens. If I
go to toolsmacro, there is nothing there. When I go back to VBE, it is
there in module1.

"Tom Hutchins" wrote:

The following event code displays an inputbox with a prompt when the workbook
is opened, and won't go away (unless you stop the macro by pressing
Ctrl-Break) until a valid date is entered. The date is then entered in a cell
on the hidden sheet (in this example, cell D10 on Sheet3). The current date
is supplied as a default.

Option Explicit

Private Sub Workbook_Open()
Dim NewDate
Do While Not IsDate(NewDate)
NewDate = InputBox("Please enter a date", "Input required", _
Format(Now(), "mm/dd/yyyy"))
Loop
Sheets("Sheet3").Range("D10").Value = NewDate
End Sub

Paste this code in the ThisWorkbook module of the workbook. If you are new
to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/200...e-elses-macro/

Hope this helps,

Hutch

"SixBowls" wrote:

I have a spreadsheet that requires the user to enter a date on a hidden tab.
The date is used for calculations throughout the spreadsheet.

On opening the sheet, I would like to have some sort of reminder/prompt to
update the date and if possible do the entry without unhiding the sheet.

I was pasting into module1. I pasted into ThisWorkbook (as you had already
stated)and it works great! Thanks for your help.

"Tom Hutchins" wrote:

The following event code displays an inputbox with a prompt when the workbook
is opened, and won't go away (unless you stop the macro by pressing
Ctrl-Break) until a valid date is entered. The date is then entered in a cell
on the hidden sheet (in this example, cell D10 on Sheet3). The current date
is supplied as a default.

Option Explicit

Private Sub Workbook_Open()
Dim NewDate
Do While Not IsDate(NewDate)
NewDate = InputBox("Please enter a date", "Input required", _
Format(Now(), "mm/dd/yyyy"))
Loop
Sheets("Sheet3").Range("D10").Value = NewDate
End Sub

Paste this code in the ThisWorkbook module of the workbook. If you are new
to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/200...e-elses-macro/

Hope this helps,

Hutch

"SixBowls" wrote:

I have a spreadsheet that requires the user to enter a date on a hidden tab.
The date is used for calculations throughout the spreadsheet.

On opening the sheet, I would like to have some sort of reminder/prompt to
update the date and if possible do the entry without unhiding the sheet.