Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using permissions to share a spreadsheet....
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using permissions to share a spreadsheet....
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using permissions to share a spreadsheet....
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using permissions to share a spreadsheet....
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need to share a spreadsheet but only certain rows | Excel Worksheet Functions | |||
Permissions | Excel Worksheet Functions | |||
Reference another spreadsheet on a share drive | New Users to Excel | |||
Setting up Permissions or Security selection spreadsheet | Excel Discussion (Misc queries) | |||
Share Excel spreadsheet | Excel Discussion (Misc queries) |