Security

[JATHGAS]

I send out weekly sales data to 75 sales reps. The file contains data for all sales reps. The data is then used and read in a second excel file that summarizes the data for each rep. This second file is protected so that all cells are locked and hidden. The reps can't change the sheet to view others' data. Right now, however, they can open the workbook containing the data and look at it. My boss wants me to add salary info to this data but needs to be assured that this information can not be directly viewed.

So, does anyone know of a way to hide a sheet and not allow for the sheet to be unhidden?

TIA

[garysenter]

do you still need the answer?

[The Gunslinger]

there are a couple of things you will need to do for this one, yes you can hide the sheet, have a look in the excel help file for 'visible' or search here (if i remember correctly there is a 'veryhidden' option available).... if you hide the sheet, then ensure it is hidden when you save your workbook, and that in the worksheet open event you instruct it to hide it again just incase. protect both the worksheets and workbook (you will need to unprotect and the reprotect the workbook from within the code to set a sheet to visible=false.
protect the project properties with a password as well, and i would look at the 'onkey event' to disable access to the vba editor that way, and also have a search here for disabling menu options.
Placing salary details in a workbook and then hiding the information will need carefull testing, for example, what happens if the user sets macros to off, or presses ctrl+break while your code is running ? .... it is possible but willl need testing carefully
The other thing that comes to mind is an add-in ... maybe you could put the info in an add-in ... and only give the add-in to your boss, have your code reference the add-in only if the boss enters a password first (i think you can do that...have not used add-in and references much)
you can also look at setting a password for when the workbook is opened
hope this gives you somewhere to start looking

[parry]

Gunslinger is onto it.

Yup there is a very hidden option which means that when the user selects Format-Unhide the Sheets option is greyed out. If they know code they could get around it though.

To hide the sheet with code run the following which will make all the sheets that you have selected very hidden. To select multiple sheets select the CTRL key as you click on the tab. Select the sheets you want before running the macro. To unselect the sheets when your finished just click on an individual sheet tab.

Ive also included he macro to unhide the sheets. When you distribute the workbook do so without these macros in the workbook so the user cant run the macro to unhide your sheets.

Sub HideSheets()
'Select Sheets to be hidden then run the macro.
Dim c
For Each c In ActiveWindow.SelectedSheets
c.Visible = xlSheetVeryHidden
Next c
End Sub

Sub UnhideSheets()
Dim c
For Each c In Sheets
c.Visible = True
Next c
End Sub