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 toa)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

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 toa)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
>
>

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

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 toa)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
>
>

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.