We help IT Professionals succeed at work.

Excel force date selection

I'm developing an Excel file for data entry by users.  The file has 12 tabs.  The data entry area in each tab is no larger than one page.  So, if I was to print the entire file, it would be 12 pages long.

I'm trying to figure out a way to force the users to select the month the data pertains to and have the month appear in the center header of each sheet.  Multiple months across the tabs cannot be used.  The month must be the same on each sheet.

I'm not trying to insert the current month but rather a selected month.   For example, the users may need to generate a report for say, July, 2017.   I want the users to be able to open the file and be forced somewhere along the line (possibly before being able to print or save) to select the month the data pertains to.

Basically there are 2 or 3 users involved; each sharing the file (in sequence, not concurrently) and each responsible for a few of the tabs in the file.   If the first user selects a month, then if all tabs are populated with that selected month, that would be fine.  Or, maybe each user goes through the process of selecting the month but the program looks at the month selected by earlier users and prevents an alternate month from being selected.

Any ideas?  Thanks.
Watch Question

Neil FlemingConsultant and developer

You could use a User form that pops up when the workbook is opened, and requests selection of a date from some drop-downs?


OK.   But how do I do that?    I want the user to select the month and then have the month specified in the header section when printing.
Consultant and developer
Try the attached. It opens a user form when you open the workbook, forces the user to select a month and year from two list boxes, and then applies that date to each sheet in the workbook which contains a cell with "Selected Month:" in it. The date is applied to the cell to the right of this text label, as you will see.
Roy CoxGroup Finance Manager

In my opinion you are doing this back to front.

All the data should be entered into one sheet and then have one sheet only for the report. This would be populated from the master data, probably by formulas.  If the data is correctly laid out then a PivotTable would be the best approach. Supply an example file.