My spreadsheet has 120+ worksheets, and hundreds of hyperlinks between them,
back & forth (it's a product selection chart)
I need to protect the data, and want only people with permission granted to
use it. However, when I set the permissions to 'read' only, the hyperlinks
stop working.... HELP! I've spent weeks developing this, and now can't find
another way to fix the issue of protection.

See if this will work for you. What this will do is hide all sheets from
anyone who opens the workbook, asking them for an 'access code' when it is
opened, and if the proper code is provided, then all sheets are made visible.
All sheets are automatically hidden when the workbook is closed.

One key to making this effective is going to be to password protect your VBA
project. Because without doing that, a person could open the workbook and
then go into the VB Editor and
1) discover the access code, and
2) then use the workbook to their heart's content.
Password protection of the VB Project is much more secure than for the
workbook or individual worksheets. If you continue to require the password
to open the workbook (but without putting the 'read only' restriction on it),
then you will have 2 levels of access control.

Also, because the password for the VB Project is harder to discover than the
one for workbooks/worksheets, you need to definitely remember it so if you
have to alter the code later, you can. It's usually a good idea to maintain
one copy without the password applied to the VB Project just in case.

First steps: open your workbook. Add a new blank sheet at the beginning of
it, name it "Access" (or a word of your choice - just have to change it in
the code below to match).

Then press [Alt]+[F11] to enter the VB Editor. Find the "ThisWorkbook"
object in the VBAProject window. Either double-click it or right click on it
and choose [View Code] to open the workbook's code module. Copy the code
below and paste it into it. Make changes to the 'Access' sheet's name in the
code if you don't use 'Access' as the new, empty sheet name. And change the
definition of the access code to suit your mood.

Save the workbook with a new name that indicates that it is unprotected by a
password. This is your safety copy. Return to the VB Editor and Choose
[Tools] from the menu, and pick VBAProject Properties from the list. Select
the [Protection] tab and tick the "Lock Project for Viewing" option. Enter a
password, confirm it, and click OK.

Close the VB Editor. Save the workbook with the name you will be using to
make it available to others for use. You should pretty much be done except
for testing that the code works properly and that the VB Project is truly
locked from curious eyes.

Close the workbook. Open it back up. Remember that Macros must be enabled
for the code to work. If you enter the wrong access code, well, you'll have
to close and reopen the workbook to get another try. But all sheets except
for the 'Access' should be invisible until the proper access code is entered.
Even trying to use the Format -- Sheet -- Unhide menu options won't work
because the sheets are hidden from appearing in that list. Press [Alt]+[F11]
to try to view the VBA Project and code - you should not be able to gain
access to it without entering the password you assigned to it.

I hope this helps with your issue. Remember that you may still want to
protect individual sheets so that you can control what cells on them may be
altered by the authorized users.

"RachelE27" wrote:

My spreadsheet has 120+ worksheets, and hundreds of hyperlinks between them,
back & forth (it's a product selection chart)
I need to protect the data, and want only people with permission granted to
use it. However, when I set the permissions to 'read' only, the hyperlinks
stop working.... HELP! I've spent weeks developing this, and now can't find
another way to fix the issue of protection.

Thanks, but I can't get this working.... when I get to the [View Code] stage,
nothing comes up, so I don't have any code to paste....
Will this solution prevent recipents of my document from copy/pasting, or
saving as something new? That's what I need to avoid....
Thanks again

"JLatham" wrote:

See if this will work for you. What this will do is hide all sheets from
anyone who opens the workbook, asking them for an 'access code' when it is
opened, and if the proper code is provided, then all sheets are made visible.
All sheets are automatically hidden when the workbook is closed.

One key to making this effective is going to be to password protect your VBA
project. Because without doing that, a person could open the workbook and
then go into the VB Editor and
1) discover the access code, and
2) then use the workbook to their heart's content.
Password protection of the VB Project is much more secure than for the
workbook or individual worksheets. If you continue to require the password
to open the workbook (but without putting the 'read only' restriction on it),
then you will have 2 levels of access control.

Also, because the password for the VB Project is harder to discover than the
one for workbooks/worksheets, you need to definitely remember it so if you
have to alter the code later, you can. It's usually a good idea to maintain
one copy without the password applied to the VB Project just in case.

First steps: open your workbook. Add a new blank sheet at the beginning of
it, name it "Access" (or a word of your choice - just have to change it in
the code below to match).

Then press [Alt]+[F11] to enter the VB Editor. Find the "ThisWorkbook"
object in the VBAProject window. Either double-click it or right click on it
and choose [View Code] to open the workbook's code module. Copy the code
below and paste it into it. Make changes to the 'Access' sheet's name in the
code if you don't use 'Access' as the new, empty sheet name. And change the
definition of the access code to suit your mood.

Save the workbook with a new name that indicates that it is unprotected by a
password. This is your safety copy. Return to the VB Editor and Choose
[Tools] from the menu, and pick VBAProject Properties from the list. Select
the [Protection] tab and tick the "Lock Project for Viewing" option. Enter a
password, confirm it, and click OK.

Close the VB Editor. Save the workbook with the name you will be using to
make it available to others for use. You should pretty much be done except
for testing that the code works properly and that the VB Project is truly
locked from curious eyes.

Close the workbook. Open it back up. Remember that Macros must be enabled
for the code to work. If you enter the wrong access code, well, you'll have
to close and reopen the workbook to get another try. But all sheets except
for the 'Access' should be invisible until the proper access code is entered.
Even trying to use the Format -- Sheet -- Unhide menu options won't work
because the sheets are hidden from appearing in that list. Press [Alt]+[F11]
to try to view the VBA Project and code - you should not be able to gain
access to it without entering the password you assigned to it.

I hope this helps with your issue. Remember that you may still want to
protect individual sheets so that you can control what cells on them may be
altered by the authorized users.

"RachelE27" wrote:

My spreadsheet has 120+ worksheets, and hundreds of hyperlinks between them,
back & forth (it's a product selection chart)
I need to protect the data, and want only people with permission granted to
use it. However, when I set the permissions to 'read' only, the hyperlinks
stop working.... HELP! I've spent weeks developing this, and now can't find
another way to fix the issue of protection.

Rachel,
My apology for not including the code you need. Here it is:

Const entrySheetName = "Access"
Private Sub Workbook_Open()
'the access code can be a word or phrase
'while only one is used here for all of
'the authorized users, with more 'maintenance'
'you could have a separate one for groups or
'even individuals
Const accessCode = "access code assigned"
Dim pwEntry As String
Dim anyWS As Worksheet

pwEntry = InputBox("Enter your access code:", _
"Password Required", "")
If pwEntry < accessCode Then
Exit Sub
End If
Application.ScreenUpdating = False
For Each anyWS In ThisWorkbook.Worksheets
anyWS.Visible = xlSheetVisible
Next
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim anyWS As Worksheet
For Each anyWS In ThisWorkbook.Worksheets
If anyWS.Name < entrySheetName Then
anyWS.Visible = xlSheetVeryHidden
End If
Next
End Sub


Now - this will NOT restrict the user from doing anything else with the
workbook, such as Save As with another name. It is simply preventing them
from seeing the contents of the workbook unless they provide the correct
access code. With the VBAProject protected, it should work fairly well, but
no security system is perfect.



"RachelE27" wrote:

Thanks, but I can't get this working.... when I get to the [View Code] stage,
nothing comes up, so I don't have any code to paste....
Will this solution prevent recipents of my document from copy/pasting, or
saving as something new? That's what I need to avoid....
Thanks again

"JLatham" wrote:

See if this will work for you. What this will do is hide all sheets from
anyone who opens the workbook, asking them for an 'access code' when it is
opened, and if the proper code is provided, then all sheets are made visible.
All sheets are automatically hidden when the workbook is closed.

One key to making this effective is going to be to password protect your VBA
project. Because without doing that, a person could open the workbook and
then go into the VB Editor and
1) discover the access code, and
2) then use the workbook to their heart's content.
Password protection of the VB Project is much more secure than for the
workbook or individual worksheets. If you continue to require the password
to open the workbook (but without putting the 'read only' restriction on it),
then you will have 2 levels of access control.

Also, because the password for the VB Project is harder to discover than the
one for workbooks/worksheets, you need to definitely remember it so if you
have to alter the code later, you can. It's usually a good idea to maintain
one copy without the password applied to the VB Project just in case.

First steps: open your workbook. Add a new blank sheet at the beginning of
it, name it "Access" (or a word of your choice - just have to change it in
the code below to match).

Then press [Alt]+[F11] to enter the VB Editor. Find the "ThisWorkbook"
object in the VBAProject window. Either double-click it or right click on it
and choose [View Code] to open the workbook's code module. Copy the code
below and paste it into it. Make changes to the 'Access' sheet's name in the
code if you don't use 'Access' as the new, empty sheet name. And change the
definition of the access code to suit your mood.

Save the workbook with a new name that indicates that it is unprotected by a
password. This is your safety copy. Return to the VB Editor and Choose
[Tools] from the menu, and pick VBAProject Properties from the list. Select
the [Protection] tab and tick the "Lock Project for Viewing" option. Enter a
password, confirm it, and click OK.

Close the VB Editor. Save the workbook with the name you will be using to
make it available to others for use. You should pretty much be done except
for testing that the code works properly and that the VB Project is truly
locked from curious eyes.

Close the workbook. Open it back up. Remember that Macros must be enabled
for the code to work. If you enter the wrong access code, well, you'll have
to close and reopen the workbook to get another try. But all sheets except
for the 'Access' should be invisible until the proper access code is entered.
Even trying to use the Format -- Sheet -- Unhide menu options won't work
because the sheets are hidden from appearing in that list. Press [Alt]+[F11]
to try to view the VBA Project and code - you should not be able to gain
access to it without entering the password you assigned to it.

I hope this helps with your issue. Remember that you may still want to
protect individual sheets so that you can control what cells on them may be
altered by the authorized users.

"RachelE27" wrote:

My spreadsheet has 120+ worksheets, and hundreds of hyperlinks between them,
back & forth (it's a product selection chart)
I need to protect the data, and want only people with permission granted to
use it. However, when I set the permissions to 'read' only, the hyperlinks
stop working.... HELP! I've spent weeks developing this, and now can't find
another way to fix the issue of protection.