0

I'am looking for a VBScript solution.

I can protect a worksheet:

ActiveSheet.Protect "password"

I can hide a worksheet:

ActiveSheet.Visible = False

But the user still can unhide the worksheet in Excel without a password. Any solution for that? Thanks

Mischka
  • 3
  • 4

1 Answers1

0

Hide without possibility of unhiding from Excel UI:

ActiveSheet.Visible = xlSheetVeryHidden

Probably in VBScript it will be ActiveSheet.Visible = 2

EDIT: After added clarification in comment:

I think the only way to achieve it will be to add on Activate event code to hide it always and protect VBA project by password. Something like below.

Private Sub Worksheet_Activate()
    ActiveSheet.Visible = xlVeryHidden
End Sub

Then you can add Sub or Function to unhide this sheet with hard-coded password (password will be passed as argument and verified against hard-coded string). In this Sub you can set some global variable to bypass Activate logic.

Then unhiding from VBA Immediate window will also not work. In BeforeSave event you can hide this sheet to have it always hidden for other users.

Not sure if this fullfils your needs but I think it will be hard to achieve more.

smartobelix
  • 748
  • 5
  • 16
  • Sounds good and grey out the menu to unhide the sheet, yes. The problem is, i want to unhide it, but only by password. Sometimes i have to see the sheet values. (Sorry i forgot that in my post) – Mischka Jun 12 '17 at 12:59
  • the other problem is, i can ALT+F11 and set the visibility to 0 without a password :( – Mischka Jun 12 '17 at 13:13
  • One more solution to hide this sensitive data could be to store them in separate workbook (fully password protected) and open them in background instance of Excel - like described here: [Open Excel file for reading with VBA without display](https://stackoverflow.com/questions/579797/open-excel-file-for-reading-with-vba-without-display) – smartobelix Jun 12 '17 at 13:57
  • Ok, i think it is not possible in VBS. I have an excel file and vbs code on the first worksheet (need it for UFT/QTP test cases). The user get all test results in all the other worksheets. I will hide the code for users and password protect it. I will take the first solution from smartobelix, hope the users doesn't know Alt+F11^^ Thanks a lot guys ... – Mischka Jun 13 '17 at 04:50