You said "I don’t think I mind having one spreadsheet with all the information since IT backs up all contents of the shared drives nightly and in past experiences we have been able to successfully retrieve lost data" but how would the Directors feel if the workbook was 'lost' late afternoon and they were asked to re-input everything they had done that day?
I know that it would be possible, using VBA, to have "very hidden" worksheets that could be made visible on giving the correct password from a prompt received on opening the workbook:
Code:
Private Sub Workbook_Open()
Dim wb As Workbook
Dim strPassword As String
Set wb = ThisWorkbook
strPassword = InputBox("Password?")
If strPassword = "Test" Then
wb.Worksheets("Test").Visible = xlSheetVisible
wb.Worksheets("Test2").Visible = xlSheetVeryHidden
End If
Set wb = Nothing
End Sub
The above is a very 'simple' example but would need to be supported with Save & Close events to again hide all the worksheets (except one).
However, some of the 'Share Workbook' options (e.g. shared workbook password, that you would not need anyway) seem to prevent the 'Open' event from firing. Remember that I don't like shared workbooks! In my test I noticed that you cannot have 'Tables' in a shared workbook and I really like using them. Because I cannot test it, I don't know whether/how that would really work when more than one person opens the workbook.
It is difficult to know exactly how I would be proceed without seeing the workbooks (and I would
not accept any offer for them to be made available - there are several reasons for this) but my current thoughts are:
1. Each Director would have their own copy of the workbook with only their worksheets/data.
2. The workbooks would have a macro which, when run, would copy the relevant worksheets* to a 'master' drive/folder, overwriting previous versions.
3. A MASTER workbook would have a macro which, when run, would merge the required worksheets* and produce the required reports.
* I would use a "Control" worksheet to list the name of the worksheets to be copied
(I don't think that you mentioned your level of VBA skills)
An alternative to "3", and the one that I would actually use, would be to have those workbooks (in the 'master' drive/folder) linked to an Access database as tables - my MASTER workbook would have those Access tables linked to it and refreshed when the workbook is opened. There would be VBA code to make 'static' copies of the workbook with its data as, for example, end-of-month historic copies. This alternative would require much less VBA work and would be much better if the column headers differ in each workbook. I do actually use this technique in my Data Warehouse to enable users to pass data to Access which then produces a formatted Access Report "in real time" without them having to open the Access application.
ffice
></o
