Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to hidden sheets
Hi.
I have tried several approaches but cant quite get what I am looking for (previous recommendation resulted in code errors). I have a workbook that is accesible as 'read only' unless someone knows the password to gain control access. The thing is, I have several sheets (3) I dont want people accessing at all - unless they open the workbook with the password. The only thing I could think of is to:(a)somehow limit the access to the sheets from 'read only' mode (b) protect the workbook and hide the sheets - then have macro that would unprotect the workbook and unhide the sheets on command. I'd rather do option (a) which seems a lot simpler however, iIf I have to do option (b), then of course I would need to do the opposite autmoatically on workbook close (hide the sheets, and protect the workbook). Am I asking too much? Or does anyone have any previous coding I could use and alter? Thanx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to hidden sheets
How about setting those three sheets as VERY hidden? That is,
sheets("xSheet").visible = xlVeryHidden Then the sheets are ONLY accessible through code or through the VB Editor (which you can protect with a password). Matthew Pfluger "J.W. Aldridge" wrote: Hi. I have tried several approaches but cant quite get what I am looking for (previous recommendation resulted in code errors). I have a workbook that is accesible as 'read only' unless someone knows the password to gain control access. The thing is, I have several sheets (3) I dont want people accessing at all - unless they open the workbook with the password. The only thing I could think of is to:(a)somehow limit the access to the sheets from 'read only' mode (b) protect the workbook and hide the sheets - then have macro that would unprotect the workbook and unhide the sheets on command. I'd rather do option (a) which seems a lot simpler however, iIf I have to do option (b), then of course I would need to do the opposite autmoatically on workbook close (hide the sheets, and protect the workbook). Am I asking too much? Or does anyone have any previous coding I could use and alter? Thanx |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to hidden sheets
I have tried that route...
Got two codes but the unhide returns the error : Unable to get the Visible property of the worksheet class. (then it highlights .Visible = xlSheetVisible) The hide macro runs perfect though. When I run the unhide code, I need it to prompt for the password before completing. Sub unHideModeratelyWell2() Dim shArray As Variant Dim i As Long shArray = Array("INFO SHEET", "ROSTER", "TIMESHEET", "PRODUCTION") For i = LBound(shArray) To UBound(shArray) With Sheets(shArray(i)) .Unprotect Password:="pcp123" .Visible = xlSheetVisible End With Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to hidden sheets
I have a similar workbook that is only opened for edit by myself and two
other people in my company. If you have a small number of people who actualy edit the data and they do not change often, this might work for you. First in your VBA window and create a new module and paste this code in: Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Function fOSUserName() As String ' Returns the network login name Dim lngLen As Long, lngX As Long Dim strUserName As String strUserName = String$(254, 0) lngLen = 255 lngX = apiGetUserName(strUserName, lngLen) If (lngX 0) Then fOSUserName = Left$(strUserName, lngLen - 1) Else fOSUserName = vbNullString End If End Function All this code does is capture the users network name. Then put this procedure in: Sub UnhideUsers() If fOSUsername < "PJFry" And fOSUserName < "EnterNextUser" Then Exit Sub End If 'Unhides and unprotects each sheet in the workbook For Each ws In ThisWorkbook.Worksheets With ws .Visible = xlSheetVisible .Unprotect End With Next ws End Sub Once again, if you have frequent changes to the users, then this approach is not the best; however, if the users are static, go for it. PJ "J.W. Aldridge" wrote: Hi. I have tried several approaches but cant quite get what I am looking for (previous recommendation resulted in code errors). I have a workbook that is accesible as 'read only' unless someone knows the password to gain control access. The thing is, I have several sheets (3) I dont want people accessing at all - unless they open the workbook with the password. The only thing I could think of is to:(a)somehow limit the access to the sheets from 'read only' mode (b) protect the workbook and hide the sheets - then have macro that would unprotect the workbook and unhide the sheets on command. I'd rather do option (a) which seems a lot simpler however, iIf I have to do option (b), then of course I would need to do the opposite autmoatically on workbook close (hide the sheets, and protect the workbook). Am I asking too much? Or does anyone have any previous coding I could use and alter? Thanx |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to hidden sheets
Thanx.
Although password access is limited, many other viewers will have read only access. Is there any reason the original code I mentioned above would not work? Any suggestions on fixing it? Thanx again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automate Access from Excel to run hidden | Excel Programming | |||
"With Sheets" Issue - macro on one sheet to affect hidden rows on other sheets | Excel Programming | |||
Access to hidden cells | Excel Worksheet Functions | |||
avoid access of hidden sheets | Excel Programming |