Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password to hide and unhide sheets
The only way u can allow selective access to hide r unhide
a worksheet is to protect the workbook with a password. If u set a password to protect the workbook, unless u unprotect the workbook u cannot hide r unhide a worksheet. So while opening the workbook the code will be workbook.protect "password" when the user wants to hide/unhide a sheet workbook.unprotect "password" worksheet.activate worksheet.hidden=true/false workbook.protect "password" Veera -----Original Message----- I am looking for a way to hide a sheet for most of the users. But some of the users should be able to see the sheets. Therefore I am looking for a macro that unhides the sheet with a password the selected users knows. I am all out of ideas, and wondering if somebody knows a way. Thanks Nicolay . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password to hide and unhide sheets
I have a wb with the five sheets named below plus a ws named "Control".
Change the references as needed to meet you needs. I run this in my Workbook_Open event for a wb that does what you want: Function GetValidInput() As String Dim i As String i = InputBox("Please enter your name, capitalizing the first letter:") Select Case i Case Is = "" msgBox "You have not entered a valid name. You will have to press OK to let this file close and try again." ThisWorkbook.Save ThisWorkbook.Close Case Is = "Margaret" Worksheets("Margaret").Visible = True Worksheets("Margaret").Select ActiveSheet.Range("A1").Select Case Is = "Esther" Worksheets("Esther").Visible = True Worksheets("Esther").Select ActiveSheet.Range("A1").Select Case Is = "George" Worksheets("George").Visible = True Worksheets("George").Select ActiveSheet.Range("A1").Select Case Is = "Lloyd" Worksheets("Lloyd").Visible = True Worksheets("Lloyd").Select ActiveSheet.Range("A1").Select Case Is = "Jimmy" Worksheets("Jimmy").Visible = True Worksheets("Jimmy").Select ActiveSheet.Range("A1").Select Case Else msgBox "You have not entered a valid name. You will have to press OK to let this file close and try again." ThisWorkbook.Save ThisWorkbook.Close End Select End Function -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Veera" wrote in message ... The only way u can allow selective access to hide r unhide a worksheet is to protect the workbook with a password. If u set a password to protect the workbook, unless u unprotect the workbook u cannot hide r unhide a worksheet. So while opening the workbook the code will be workbook.protect "password" when the user wants to hide/unhide a sheet workbook.unprotect "password" worksheet.activate worksheet.hidden=true/false workbook.protect "password" Veera -----Original Message----- I am looking for a way to hide a sheet for most of the users. But some of the users should be able to see the sheets. Therefore I am looking for a macro that unhides the sheet with a password the selected users knows. I am all out of ideas, and wondering if somebody knows a way. Thanks Nicolay . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password to hide and unhide sheets
Veera,
Another way would be to make the sheet very hidden. From the Workbook.Before_Save Event Worksheets("Sheet2").Visible = xlVeryHidden (Insures that the workbook is always saved with Sheet2 very hidden Then you can have a UserForm called up from a Command Button. Command Button Code (from the Controls Toolbox) would be: UserForm1.Show And the UserForm would be set up with a TextBox and a CommandButton. Coding as follows: Private Sub UserForm_Activate() If cCount 2 Then Unload UserForm1 End If End Sub Private Sub CommandButton1_Click() If cCount 2 Then Unload UserForm1 Else If TextBox1.Value = "abc" Then Worksheets("Sheet2").Visible = True Else cCount = cCount + 1 End If End If End Sub And lastly.....at the top of a regular module.....(not the UserForm module). Public cCount as Integer The above will allow the user three attempts at the password and won't give them the option again until they close and reopen the workbook. You should then Lock and Password protect the VBA code from prying eyes but be aware that an experienced user can circumvent any passwords John Veera wrote: The only way u can allow selective access to hide r unhide a worksheet is to protect the workbook with a password. If u set a password to protect the workbook, unless u unprotect the workbook u cannot hide r unhide a worksheet. So while opening the workbook the code will be workbook.protect "password" when the user wants to hide/unhide a sheet workbook.unprotect "password" worksheet.activate worksheet.hidden=true/false workbook.protect "password" Veera -----Original Message----- I am looking for a way to hide a sheet for most of the users. But some of the users should be able to see the sheets. Therefore I am looking for a macro that unhides the sheet with a password the selected users knows. I am all out of ideas, and wondering if somebody knows a way. Thanks Nicolay . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Toolbar buttons to hide/unhide sheets | Excel Discussion (Misc queries) | |||
Macro-Hide & Unhide Sheets with condition | Excel Discussion (Misc queries) | |||
Hide or unhide sheets based on cell | Excel Discussion (Misc queries) | |||
Hide Unhide | Excel Discussion (Misc queries) | |||
How do I hide a worksheet in Excel and use a password to un-hide . | Excel Discussion (Misc queries) |