I have a shared document that needs to be set up so that individuals can access only the worksheet for them (no others) as well as protected cells within that worksheet. I have set the passwords for the sheets as far as the data is concerned, but is it possible to protect each worksheet with an additional password or can only one password be used per worksheet? The trouble is that one individual could/would enter data on anothers worksheet.
Each worksheet can have a unique password. All you have to do is assign that password to the sheet. Of course you also have to remember what the password is for each of the sheets.
As long as the people using the workbook are 'honest' and all you're interested in doing is keeping users from accidentally altering someone else's data or using the wrong sheet, then things are fine. But remember that the encryption of the password for both workbook and worksheet protection is very weak and easily cracked - so it's not much for providing true 'security' type protection.
You could put code in each worksheet's _Activate() event to prompt for its password, and if the proper password isn't given, leave the sheet locked up. Another person posting a question about this kind of thing had such a deal set up: when you activated a sheet, it first hid all of the columns on that sheet and requested the password. If the proper password wasn't given, then the columns remained hidden (and the sheet remained protected), but if the proper password was given, then the columns were made visible and sheet protection was removed. The sheet's _Deactivate() event handler was set up to always put the sheet back into protected state with the columns hidden again.
"Kristi" wrote: > I have a shared document that needs to be set up so that individuals can > access only the worksheet for them (no others) as well as protected cells > within that worksheet. I have set the passwords for the sheets as far as the > data is concerned, but is it possible to protect each worksheet with an > additional password or can only one password be used per worksheet? The > trouble is that one individual could/would enter data on anothers worksheet.
"JLatham" wrote: > Each worksheet can have a unique password. All you have to do is assign that > password to the sheet. Of course you also have to remember what the password > is for each of the sheets.
> As long as the people using the workbook are 'honest' and all you're > interested in doing is keeping users from accidentally altering someone > else's data or using the wrong sheet, then things are fine. But remember > that the encryption of the password for both workbook and worksheet > protection is very weak and easily cracked - so it's not much for providing > true 'security' type protection.
> You could put code in each worksheet's _Activate() event to prompt for its > password, and if the proper password isn't given, leave the sheet locked up. > Another person posting a question about this kind of thing had such a deal > set up: when you activated a sheet, it first hid all of the columns on that > sheet and requested the password. If the proper password wasn't given, then > the columns remained hidden (and the sheet remained protected), but if the > proper password was given, then the columns were made visible and sheet > protection was removed. The sheet's _Deactivate() event handler was set up > to always put the sheet back into protected state with the columns hidden > again.
> > I have a shared document that needs to be set up so that individuals can > > access only the worksheet for them (no others) as well as protected cells > > within that worksheet. I have set the passwords for the sheets as far as the > > data is concerned, but is it possible to protect each worksheet with an > > additional password or can only one password be used per worksheet? The > > trouble is that one individual could/would enter data on anothers worksheet.
You can get around this either of two ways but I'll add the warning that Excel's internal security is quite weak and passwords can be broken.
1. Have workbook_open code that checks to see who is the log-in user and open just that sheet with all others veryhidden or a blank(dummy) sheet if users disable macros.
2. Each user has his own password. The workbook opens with a blank(dummy) sheet and an InputBox for user to enter his password which opens his sheet only.
No password to open the workbook, just code to make a user's sheet visible.
In the Thisworkbook Module....................
Private Sub Workbook_Open() Dim pword As String Select Case Environ("Username") 'if a login is not used and each user has his own sheet password change to 'pword = InputBox("Enter Your Password") 'Select Case pword Case Is = "Gord": Sheets("Gordsheet").Visible = True Sheets("Dummy").Visible = False Case Is = "Pete": Sheets("Petesheet").Visible = True Sheets("Dummy").Visible = False
End Select End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim sht As Worksheet Application.ScreenUpdating = False Sheets("Dummy").Visible = xlSheetVisible For Each sht In ActiveWorkbook.Sheets If sht.Name <> "Dummy" Then sht.Visible = xlSheetVeryHidden End If Next sht Application.ScreenUpdating = True ThisWorkbook.Save End Sub
You would protect the project so's prying eyes cannot see the code and passwords.
You may also want as administrator to be able to open all sheets for editing.
Add these lines to the Workbook_Open code above End Select
Case Is = "Admin" For n = 1 To Sheets.Count Sheets(n).Visible = True Next n
Gord Dibben MS Excel MVP
On Tue, 27 Nov 2007 11:15:02 -0800, Kristi <Kri...@discussions.microsoft.com> wrote:
>I have a shared document that needs to be set up so that individuals can >access only the worksheet for them (no others) as well as protected cells >within that worksheet. I have set the passwords for the sheets as far as the >data is concerned, but is it possible to protect each worksheet with an >additional password or can only one password be used per worksheet? The >trouble is that one individual could/would enter data on anothers worksheet.
To put code in a worksheet's event handlers, right-click on the worksheet's name tab and choose View Code from the popup list. You can choose Worksheet from the left hand pull down at the top, and which event from the right hand pull down list. You'll have a 'stub' for _SelectionChange() which you can delete. In the case of the code I pointed to, you wouldn't need the pull-downs, just copy and paste the code into that module. Note that you have to do this for each worksheet, and of course modify the code to use the appropriate password for it.
To get to where you need to be for the solution that Gord has offered, right-click on the Excel icon immediately to the left of the word "File" in the main Excel menu toolbar and again, choose [View Code] from that popup list and copy/paste his code if you choose to go that route.
"Kristi" wrote: > How/where do I insert the code on the separate worksheets? Can it be the > same password as the data locked password?
> "JLatham" wrote:
> > Each worksheet can have a unique password. All you have to do is assign that > > password to the sheet. Of course you also have to remember what the password > > is for each of the sheets.
> > As long as the people using the workbook are 'honest' and all you're > > interested in doing is keeping users from accidentally altering someone > > else's data or using the wrong sheet, then things are fine. But remember > > that the encryption of the password for both workbook and worksheet > > protection is very weak and easily cracked - so it's not much for providing > > true 'security' type protection.
> > You could put code in each worksheet's _Activate() event to prompt for its > > password, and if the proper password isn't given, leave the sheet locked up. > > Another person posting a question about this kind of thing had such a deal > > set up: when you activated a sheet, it first hid all of the columns on that > > sheet and requested the password. If the proper password wasn't given, then > > the columns remained hidden (and the sheet remained protected), but if the > > proper password was given, then the columns were made visible and sheet > > protection was removed. The sheet's _Deactivate() event handler was set up > > to always put the sheet back into protected state with the columns hidden > > again.
> > > I have a shared document that needs to be set up so that individuals can > > > access only the worksheet for them (no others) as well as protected cells > > > within that worksheet. I have set the passwords for the sheets as far as the > > > data is concerned, but is it possible to protect each worksheet with an > > > additional password or can only one password be used per worksheet? The > > > trouble is that one individual could/would enter data on anothers worksheet.
> You can get around this either of two ways but I'll add the warning that Excel's > internal security is quite weak and passwords can be broken.
> 1. Have workbook_open code that checks to see who is the log-in user and open > just that sheet with all others veryhidden or a blank(dummy) sheet if users > disable macros.
> 2. Each user has his own password. The workbook opens with a blank(dummy) sheet > and an InputBox for user to enter his password which opens his sheet only.
> No password to open the workbook, just code to make a user's sheet visible.
> In the Thisworkbook Module....................
> Private Sub Workbook_Open() > Dim pword As String > Select Case Environ("Username") > 'if a login is not used and each user has his own sheet password change to > 'pword = InputBox("Enter Your Password") > 'Select Case pword > Case Is = "Gord": Sheets("Gordsheet").Visible = True > Sheets("Dummy").Visible = False > Case Is = "Pete": Sheets("Petesheet").Visible = True > Sheets("Dummy").Visible = False
> End Select > End Sub
> Private Sub Workbook_BeforeClose(Cancel As Boolean) > Dim sht As Worksheet > Application.ScreenUpdating = False > Sheets("Dummy").Visible = xlSheetVisible > For Each sht In ActiveWorkbook.Sheets > If sht.Name <> "Dummy" Then > sht.Visible = xlSheetVeryHidden > End If > Next sht > Application.ScreenUpdating = True > ThisWorkbook.Save > End Sub
> You would protect the project so's prying eyes cannot see the code and > passwords.
> You may also want as administrator to be able to open all sheets for editing.
> Add these lines to the Workbook_Open code above End Select
> Case Is = "Admin" > For n = 1 To Sheets.Count > Sheets(n).Visible = True > Next n
> Gord Dibben MS Excel MVP
> On Tue, 27 Nov 2007 11:15:02 -0800, Kristi <Kri...@discussions.microsoft.com> > wrote:
> >I have a shared document that needs to be set up so that individuals can > >access only the worksheet for them (no others) as well as protected cells > >within that worksheet. I have set the passwords for the sheets as far as the > >data is concerned, but is it possible to protect each worksheet with an > >additional password or can only one password be used per worksheet? The > >trouble is that one individual could/would enter data on anothers worksheet.
"JLatham" wrote: > To put code in a worksheet's event handlers, right-click on the worksheet's > name tab and choose View Code from the popup list. You can choose Worksheet > from the left hand pull down at the top, and which event from the right hand > pull down list. You'll have a 'stub' for _SelectionChange() which you can > delete. In the case of the code I pointed to, you wouldn't need the > pull-downs, just copy and paste the code into that module. Note that you > have to do this for each worksheet, and of course modify the code to use the > appropriate password for it.
> To get to where you need to be for the solution that Gord has offered, > right-click on the Excel icon immediately to the left of the word "File" in > the main Excel menu toolbar and again, choose [View Code] from that popup > list and copy/paste his code if you choose to go that route.
> "Kristi" wrote:
> > How/where do I insert the code on the separate worksheets? Can it be the > > same password as the data locked password?
> > "JLatham" wrote:
> > > Each worksheet can have a unique password. All you have to do is assign that > > > password to the sheet. Of course you also have to remember what the password > > > is for each of the sheets.
> > > As long as the people using the workbook are 'honest' and all you're > > > interested in doing is keeping users from accidentally altering someone > > > else's data or using the wrong sheet, then things are fine. But remember > > > that the encryption of the password for both workbook and worksheet > > > protection is very weak and easily cracked - so it's not much for providing > > > true 'security' type protection.
> > > You could put code in each worksheet's _Activate() event to prompt for its > > > password, and if the proper password isn't given, leave the sheet locked up. > > > Another person posting a question about this kind of thing had such a deal > > > set up: when you activated a sheet, it first hid all of the columns on that > > > sheet and requested the password. If the proper password wasn't given, then > > > the columns remained hidden (and the sheet remained protected), but if the > > > proper password was given, then the columns were made visible and sheet > > > protection was removed. The sheet's _Deactivate() event handler was set up > > > to always put the sheet back into protected state with the columns hidden > > > again.
> > > > I have a shared document that needs to be set up so that individuals can > > > > access only the worksheet for them (no others) as well as protected cells > > > > within that worksheet. I have set the passwords for the sheets as far as the > > > > data is concerned, but is it possible to protect each worksheet with an > > > > additional password or can only one password be used per worksheet? The > > > > trouble is that one individual could/would enter data on anothers worksheet.
"JLatham" wrote: > To put code in a worksheet's event handlers, right-click on the worksheet's > name tab and choose View Code from the popup list. You can choose Worksheet > from the left hand pull down at the top, and which event from the right hand > pull down list. You'll have a 'stub' for _SelectionChange() which you can > delete. In the case of the code I pointed to, you wouldn't need the > pull-downs, just copy and paste the code into that module. Note that you > have to do this for each worksheet, and of course modify the code to use the > appropriate password for it.
> To get to where you need to be for the solution that Gord has offered, > right-click on the Excel icon immediately to the left of the word "File" in > the main Excel menu toolbar and again, choose [View Code] from that popup > list and copy/paste his code if you choose to go that route.
> "Kristi" wrote:
> > How/where do I insert the code on the separate worksheets? Can it be the > > same password as the data locked password?
> > "JLatham" wrote:
> > > Each worksheet can have a unique password. All you have to do is assign that > > > password to the sheet. Of course you also have to remember what the password > > > is for each of the sheets.
> > > As long as the people using the workbook are 'honest' and all you're > > > interested in doing is keeping users from accidentally altering someone > > > else's data or using the wrong sheet, then things are fine. But remember > > > that the encryption of the password for both workbook and worksheet > > > protection is very weak and easily cracked - so it's not much for providing > > > true 'security' type protection.
> > > You could put code in each worksheet's _Activate() event to prompt for its > > > password, and if the proper password isn't given, leave the sheet locked up. > > > Another person posting a question about this kind of thing had such a deal > > > set up: when you activated a sheet, it first hid all of the columns on that > > > sheet and requested the password. If the proper password wasn't given, then > > > the columns remained hidden (and the sheet remained protected), but if the > > > proper password was given, then the columns were made visible and sheet > > > protection was removed. The sheet's _Deactivate() event handler was set up > > > to always put the sheet back into protected state with the columns hidden > > > again.
> > > > I have a shared document that needs to be set up so that individuals can > > > > access only the worksheet for them (no others) as well as protected cells > > > > within that worksheet. I have set the passwords for the sheets as far as the > > > > data is concerned, but is it possible to protect each worksheet with an > > > > additional password or can only one password be used per worksheet? The > > > > trouble is that one individual could/would enter data on anothers worksheet.
>I don't know how/where to enter these codes on the worksheets. I think this >would work, but I don't understand how to do it.
>"Gord Dibben" wrote:
>> One password per sheet.
>> You can get around this either of two ways but I'll add the warning that Excel's >> internal security is quite weak and passwords can be broken.
>> 1. Have workbook_open code that checks to see who is the log-in user and open >> just that sheet with all others veryhidden or a blank(dummy) sheet if users >> disable macros.
>> 2. Each user has his own password. The workbook opens with a blank(dummy) sheet >> and an InputBox for user to enter his password which opens his sheet only.
>> No password to open the workbook, just code to make a user's sheet visible.
>> In the Thisworkbook Module....................
>> Private Sub Workbook_Open() >> Dim pword As String >> Select Case Environ("Username") >> 'if a login is not used and each user has his own sheet password change to >> 'pword = InputBox("Enter Your Password") >> 'Select Case pword >> Case Is = "Gord": Sheets("Gordsheet").Visible = True >> Sheets("Dummy").Visible = False >> Case Is = "Pete": Sheets("Petesheet").Visible = True >> Sheets("Dummy").Visible = False
>> End Select >> End Sub
>> Private Sub Workbook_BeforeClose(Cancel As Boolean) >> Dim sht As Worksheet >> Application.ScreenUpdating = False >> Sheets("Dummy").Visible = xlSheetVisible >> For Each sht In ActiveWorkbook.Sheets >> If sht.Name <> "Dummy" Then >> sht.Visible = xlSheetVeryHidden >> End If >> Next sht >> Application.ScreenUpdating = True >> ThisWorkbook.Save >> End Sub
>> You would protect the project so's prying eyes cannot see the code and >> passwords.
>> You may also want as administrator to be able to open all sheets for editing.
>> Add these lines to the Workbook_Open code above End Select
>> Case Is = "Admin" >> For n = 1 To Sheets.Count >> Sheets(n).Visible = True >> Next n
>> Gord Dibben MS Excel MVP
>> On Tue, 27 Nov 2007 11:15:02 -0800, Kristi <Kri...@discussions.microsoft.com> >> wrote:
>> >I have a shared document that needs to be set up so that individuals can >> >access only the worksheet for them (no others) as well as protected cells >> >within that worksheet. I have set the passwords for the sheets as far as the >> >data is concerned, but is it possible to protect each worksheet with an >> >additional password or can only one password be used per worksheet? The >> >trouble is that one individual could/would enter data on anothers worksheet.
Kristi, I was going to offer to have you send workbook to me to look over, but Gord has beaten me to that offer, and I think to keep confusion to you to a minimum it's probably best to work with just one mentor - in this case, I'll defer to Gord, so go ahead and get in touch with him. I can't imagine either of you needing me from this point, even though he doesn't sell insurance, "You're in good hands with Gord" <g>, but if you do, just reply to this and I'll be back.
"Kristi" wrote: > Nothing happened. I'm sure I am not doing something right, I just don't know > what.
> "JLatham" wrote:
> > To put code in a worksheet's event handlers, right-click on the worksheet's > > name tab and choose View Code from the popup list. You can choose Worksheet > > from the left hand pull down at the top, and which event from the right hand > > pull down list. You'll have a 'stub' for _SelectionChange() which you can > > delete. In the case of the code I pointed to, you wouldn't need the > > pull-downs, just copy and paste the code into that module. Note that you > > have to do this for each worksheet, and of course modify the code to use the > > appropriate password for it.
> > To get to where you need to be for the solution that Gord has offered, > > right-click on the Excel icon immediately to the left of the word "File" in > > the main Excel menu toolbar and again, choose [View Code] from that popup > > list and copy/paste his code if you choose to go that route.
> > "Kristi" wrote:
> > > How/where do I insert the code on the separate worksheets? Can it be the > > > same password as the data locked password?
> > > "JLatham" wrote:
> > > > Each worksheet can have a unique password. All you have to do is assign that > > > > password to the sheet. Of course you also have to remember what the password > > > > is for each of the sheets.
> > > > As long as the people using the workbook are 'honest' and all you're > > > > interested in doing is keeping users from accidentally altering someone > > > > else's data or using the wrong sheet, then things are fine. But remember > > > > that the encryption of the password for both workbook and worksheet > > > > protection is very weak and easily cracked - so it's not much for providing > > > > true 'security' type protection.
> > > > You could put code in each worksheet's _Activate() event to prompt for its > > > > password, and if the proper password isn't given, leave the sheet locked up. > > > > Another person posting a question about this kind of thing had such a deal > > > > set up: when you activated a sheet, it first hid all of the columns on that > > > > sheet and requested the password. If the proper password wasn't given, then > > > > the columns remained hidden (and the sheet remained protected), but if the > > > > proper password was given, then the columns were made visible and sheet > > > > protection was removed. The sheet's _Deactivate() event handler was set up > > > > to always put the sheet back into protected state with the columns hidden > > > > again.
> > > > > I have a shared document that needs to be set up so that individuals can > > > > > access only the worksheet for them (no others) as well as protected cells > > > > > within that worksheet. I have set the passwords for the sheets as far as the > > > > > data is concerned, but is it possible to protect each worksheet with an > > > > > additional password or can only one password be used per worksheet? The > > > > > trouble is that one individual could/would enter data on anothers worksheet.
Jlathamsite.com.(removethis)> wrote: >Kristi, >I was going to offer to have you send workbook to me to look over, but Gord >has beaten me to that offer, and I think to keep confusion to you to a >minimum it's probably best to work with just one mentor - in this case, I'll >defer to Gord, so go ahead and get in touch with him. I can't imagine either >of you needing me from this point, even though he doesn't sell insurance, >"You're in good hands with Gord" <g>, but if you do, just reply to this and >I'll be back.
>"Kristi" wrote:
>> Nothing happened. I'm sure I am not doing something right, I just don't know >> what.
>> "JLatham" wrote:
>> > To put code in a worksheet's event handlers, right-click on the worksheet's >> > name tab and choose View Code from the popup list. You can choose Worksheet >> > from the left hand pull down at the top, and which event from the right hand >> > pull down list. You'll have a 'stub' for _SelectionChange() which you can >> > delete. In the case of the code I pointed to, you wouldn't need the >> > pull-downs, just copy and paste the code into that module. Note that you >> > have to do this for each worksheet, and of course modify the code to use the >> > appropriate password for it.
>> > To get to where you need to be for the solution that Gord has offered, >> > right-click on the Excel icon immediately to the left of the word "File" in >> > the main Excel menu toolbar and again, choose [View Code] from that popup >> > list and copy/paste his code if you choose to go that route.
>> > "Kristi" wrote:
>> > > How/where do I insert the code on the separate worksheets? Can it be the >> > > same password as the data locked password?
>> > > "JLatham" wrote:
>> > > > Each worksheet can have a unique password. All you have to do is assign that >> > > > password to the sheet. Of course you also have to remember what the password >> > > > is for each of the sheets.
>> > > > As long as the people using the workbook are 'honest' and all you're >> > > > interested in doing is keeping users from accidentally altering someone >> > > > else's data or using the wrong sheet, then things are fine. But remember >> > > > that the encryption of the password for both workbook and worksheet >> > > > protection is very weak and easily cracked - so it's not much for providing >> > > > true 'security' type protection.
>> > > > You could put code in each worksheet's _Activate() event to prompt for its >> > > > password, and if the proper password isn't given, leave the sheet locked up. >> > > > Another person posting a question about this kind of thing had such a deal >> > > > set up: when you activated a sheet, it first hid all of the columns on that >> > > > sheet and requested the password. If the proper password wasn't given, then >> > > > the columns remained hidden (and the sheet remained protected), but if the >> > > > proper password was given, then the columns were made visible and sheet >> > > > protection was removed. The sheet's _Deactivate() event handler was set up >> > > > to always put the sheet back into protected state with the columns hidden >> > > > again.
>> > > > > I have a shared document that needs to be set up so that individuals can >> > > > > access only the worksheet for them (no others) as well as protected cells >> > > > > within that worksheet. I have set the passwords for the sheets as far as the >> > > > > data is concerned, but is it possible to protect each worksheet with an >> > > > > additional password or can only one password be used per worksheet? The >> > > > > trouble is that one individual could/would enter data on anothers worksheet.
> On Tue, 27 Nov 2007 15:16:01 -0800, JLatham <HelpFrom @ > Jlathamsite.com.(removethis)> wrote:
> >Kristi, > >I was going to offer to have you send workbook to me to look over, but Gord > >has beaten me to that offer, and I think to keep confusion to you to a > >minimum it's probably best to work with just one mentor - in this case, I'll > >defer to Gord, so go ahead and get in touch with him. I can't imagine either > >of you needing me from this point, even though he doesn't sell insurance, > >"You're in good hands with Gord" <g>, but if you do, just reply to this and > >I'll be back.
> >"Kristi" wrote:
> >> Nothing happened. I'm sure I am not doing something right, I just don't know > >> what.
> >> "JLatham" wrote:
> >> > To put code in a worksheet's event handlers, right-click on the worksheet's > >> > name tab and choose View Code from the popup list. You can choose Worksheet > >> > from the left hand pull down at the top, and which event from the right hand > >> > pull down list. You'll have a 'stub' for _SelectionChange() which you can > >> > delete. In the case of the code I pointed to, you wouldn't need the > >> > pull-downs, just copy and paste the code into that module. Note that you > >> > have to do this for each worksheet, and of course modify the code to use the > >> > appropriate password for it.
> >> > To get to where you need to be for the solution that Gord has offered, > >> > right-click on the Excel icon immediately to the left of the word "File" in > >> > the main Excel menu toolbar and again, choose [View Code] from that popup > >> > list and copy/paste his code if you choose to go that route.
> >> > "Kristi" wrote:
> >> > > How/where do I insert the code on the separate worksheets? Can it be the > >> > > same password as the data locked password?
> >> > > "JLatham" wrote:
> >> > > > Each worksheet can have a unique password. All you have to do is assign that > >> > > > password to the sheet. Of course you also have to remember what the password > >> > > > is for each of the sheets.
> >> > > > As long as the people using the workbook are 'honest' and all you're > >> > > > interested in doing is keeping users from accidentally altering someone > >> > > > else's data or using the wrong sheet, then things are fine. But remember > >> > > > that the encryption of the password for both workbook and worksheet > >> > > > protection is very weak and easily cracked - so it's not much for providing > >> > > > true 'security' type protection.
> >> > > > You could put code in each worksheet's _Activate() event to prompt for its > >> > > > password, and if the proper password isn't given, leave the sheet locked up. > >> > > > Another person posting a question about this kind of thing had such a deal > >> > > > set up: when you activated a sheet, it first hid all of the columns on that > >> > > > sheet and requested the password. If the proper password wasn't given, then > >> > > > the columns remained hidden (and the sheet remained protected), but if the > >> > > > proper password was given, then the columns were made visible and sheet > >> > > > protection was removed. The sheet's _Deactivate() event handler was set up > >> > > > to always put the sheet back into protected state with the columns hidden > >> > > > again.
> >> > > > > I have a shared document that needs to be set up so that individuals can > >> > > > > access only the worksheet for them (no others) as well as protected cells > >> > > > > within that worksheet. I have set the passwords for the sheets as far as the > >> > > > > data is concerned, but is it possible to protect each worksheet with an > >> > > > > additional password or can only one password be used per worksheet? The > >> > > > > trouble is that one individual could/would enter data on anothers worksheet.
I think the method you've provided here, using the Workbook events is a good way to go. It permits Kristi to have true password protection on the individual worksheets while the individual user's password/username is acting as a pseudo-password to make the sheets they're permitted to work with visible to them. Only one small reminder to mention, and that's Excel's requirement that at least one worksheet always be visible, and it looks to me as if the Dummy sheet you've referenced is there for exactly that purpose.
"Gord Dibben" wrote: > The two sets of code I posted below will go into the Thisworkbook module.
> Jerry's second post explained how to do that.
> Which way would you like to go? Jerry's method or mine?
> If mine..............
> Log-in username with a single password for all sheets which only you know or a > separate password for each sheet which only the user knows?
> Send me a sample workbook by email with a list of some usernames or sheet > passwords and I'll see what I can come up with.
> Change the AT and DOT for my email address.
> Gord
> On Tue, 27 Nov 2007 13:19:00 -0800, Kristi <Kri...@discussions.microsoft.com> > wrote:
> >I don't know how/where to enter these codes on the worksheets. I think this > >would work, but I don't understand how to do it.
> >"Gord Dibben" wrote:
> >> One password per sheet.
> >> You can get around this either of two ways but I'll add the warning that Excel's > >> internal security is quite weak and passwords can be broken.
> >> 1. Have workbook_open code that checks to see who is the log-in user and open > >> just that sheet with all others veryhidden or a blank(dummy) sheet if users > >> disable macros.
> >> 2. Each user has his own password. The workbook opens with a blank(dummy) sheet > >> and an InputBox for user to enter his password which opens his sheet only.
> >> No password to open the workbook, just code to make a user's sheet visible.
> >> In the Thisworkbook Module....................
> >> Private Sub Workbook_Open() > >> Dim pword As String > >> Select Case Environ("Username") > >> 'if a login is not used and each user has his own sheet password change to > >> 'pword = InputBox("Enter Your Password") > >> 'Select Case pword > >> Case Is = "Gord": Sheets("Gordsheet").Visible = True > >> Sheets("Dummy").Visible = False > >> Case Is = "Pete": Sheets("Petesheet").Visible = True > >> Sheets("Dummy").Visible = False
> >> End Select > >> End Sub
> >> Private Sub Workbook_BeforeClose(Cancel As Boolean) > >> Dim sht As Worksheet > >> Application.ScreenUpdating = False > >> Sheets("Dummy").Visible = xlSheetVisible > >> For Each sht In ActiveWorkbook.Sheets > >> If sht.Name <> "Dummy" Then > >> sht.Visible = xlSheetVeryHidden > >> End If > >> Next sht > >> Application.ScreenUpdating = True > >> ThisWorkbook.Save > >> End Sub
> >> You would protect the project so's prying eyes cannot see the code and > >> passwords.
> >> You may also want as administrator to be able to open all sheets for editing.
> >> Add these lines to the Workbook_Open code above End Select
> >> Case Is = "Admin" > >> For n = 1 To Sheets.Count > >> Sheets(n).Visible = True > >> Next n
> >> Gord Dibben MS Excel MVP
> >> On Tue, 27 Nov 2007 11:15:02 -0800, Kristi <Kri...@discussions.microsoft.com> > >> wrote:
> >> >I have a shared document that needs to be set up so that individuals can > >> >access only the worksheet for them (no others) as well as protected cells > >> >within that worksheet. I have set the passwords for the sheets as far as the > >> >data is concerned, but is it possible to protect each worksheet with an > >> >additional password or can only one password be used per worksheet? The > >> >trouble is that one individual could/would enter data on anothers worksheet.
1. You must have at least one sheet as you mention.
2. Kristi can leave a 36 pt. message in the middle of this sheet stating "You have disabled macros, rendering this workbook unusable. Reopen with macros enabled"
Gord
On Tue, 27 Nov 2007 20:26:01 -0800, JLatham <HelpFrom @
Jlathamsite.com.(removethis)> wrote: >I think the method you've provided here, using the Workbook events is a good >way to go. It permits Kristi to have true password protection on the >individual worksheets while the individual user's password/username is acting >as a pseudo-password to make the sheets they're permitted to work with >visible to them. Only one small reminder to mention, and that's Excel's >requirement that at least one worksheet always be visible, and it looks to me >as if the Dummy sheet you've referenced is there for exactly that purpose.
>"Gord Dibben" wrote:
>> The two sets of code I posted below will go into the Thisworkbook module.
>> Jerry's second post explained how to do that.
>> Which way would you like to go? Jerry's method or mine?
>> If mine..............
>> Log-in username with a single password for all sheets which only you know or a >> separate password for each sheet which only the user knows?
>> Send me a sample workbook by email with a list of some usernames or sheet >> passwords and I'll see what I can come up with.
>> Change the AT and DOT for my email address.
>> Gord
>> On Tue, 27 Nov 2007 13:19:00 -0800, Kristi <Kri...@discussions.microsoft.com> >> wrote:
>> >I don't know how/where to enter these codes on the worksheets. I think this >> >would work, but I don't understand how to do it.
>> >"Gord Dibben" wrote:
>> >> One password per sheet.
>> >> You can get around this either of two ways but I'll add the warning that Excel's >> >> internal security is quite weak and passwords can be broken.
>> >> 1. Have workbook_open code that checks to see who is the log-in user and open >> >> just that sheet with all others veryhidden or a blank(dummy) sheet if users >> >> disable macros.
>> >> 2. Each user has his own password. The workbook opens with a blank(dummy) sheet >> >> and an InputBox for user to enter his password which opens his sheet only.
>> >> No password to open the workbook, just code to make a user's sheet visible.
>> >> In the Thisworkbook Module....................
>> >> Private Sub Workbook_Open() >> >> Dim pword As String >> >> Select Case Environ("Username") >> >> 'if a login is not used and each user has his own sheet password change to >> >> 'pword = InputBox("Enter Your Password") >> >> 'Select Case pword >> >> Case Is = "Gord": Sheets("Gordsheet").Visible = True >> >> Sheets("Dummy").Visible = False >> >> Case Is = "Pete": Sheets("Petesheet").Visible = True >> >> Sheets("Dummy").Visible = False
>> >> End Select >> >> End Sub
>> >> Private Sub Workbook_BeforeClose(Cancel As Boolean) >> >> Dim sht As Worksheet >> >> Application.ScreenUpdating = False >> >> Sheets("Dummy").Visible = xlSheetVisible >> >> For Each sht In ActiveWorkbook.Sheets >> >> If sht.Name <> "Dummy" Then >> >> sht.Visible = xlSheetVeryHidden >> >> End If >> >> Next sht >> >> Application.ScreenUpdating = True >> >> ThisWorkbook.Save >> >> End Sub
>> >> You would protect the project so's prying eyes cannot see the code and >> >> passwords.
>> >> You may also want as administrator to be able to open all sheets for editing.
>> >> Add these lines to the Workbook_Open code above End Select
>> >> Case Is = "Admin" >> >> For n = 1 To Sheets.Count >> >> Sheets(n).Visible = True >> >> Next n
>> >> Gord Dibben MS Excel MVP
>> >> On Tue, 27 Nov 2007 11:15:02 -0800, Kristi <Kri...@discussions.microsoft.com> >> >> wrote:
>> >> >I have a shared document that needs to be set up so that individuals can >> >> >access only the worksheet for them (no others) as well as protected cells >> >> >within that worksheet. I have set the passwords for the sheets as far as the >> >> >data is concerned, but is it possible to protect each worksheet with an >> >> >additional password or can only one password be used per worksheet? The >> >> >trouble is that one individual could/would enter data on anothers worksheet.
I was going to suggest something like that - and then figured you'd take advantage of the 'opportunity' on your own. I've done same thing in a couple of projects I've done - even including instructions on how to set Macro Security levels on the sheet, just in case.
"Gord Dibben" wrote: > I use the dummy sheet for two reasons.
> 1. You must have at least one sheet as you mention.
> 2. Kristi can leave a 36 pt. message in the middle of this sheet stating "You > have disabled macros, rendering this workbook unusable. Reopen with macros > enabled"
> Gord
> On Tue, 27 Nov 2007 20:26:01 -0800, JLatham <HelpFrom @ > Jlathamsite.com.(removethis)> wrote:
> >I think the method you've provided here, using the Workbook events is a good > >way to go. It permits Kristi to have true password protection on the > >individual worksheets while the individual user's password/username is acting > >as a pseudo-password to make the sheets they're permitted to work with > >visible to them. Only one small reminder to mention, and that's Excel's > >requirement that at least one worksheet always be visible, and it looks to me > >as if the Dummy sheet you've referenced is there for exactly that purpose.
> >"Gord Dibben" wrote:
> >> The two sets of code I posted below will go into the Thisworkbook module.
> >> Jerry's second post explained how to do that.
> >> Which way would you like to go? Jerry's method or mine?
> >> If mine..............
> >> Log-in username with a single password for all sheets which only you know or a > >> separate password for each sheet which only the user knows?
> >> Send me a sample workbook by email with a list of some usernames or sheet > >> passwords and I'll see what I can come up with.
> >> Change the AT and DOT for my email address.
> >> Gord
> >> On Tue, 27 Nov 2007 13:19:00 -0800, Kristi <Kri...@discussions.microsoft.com> > >> wrote:
> >> >I don't know how/where to enter these codes on the worksheets. I think this > >> >would work, but I don't understand how to do it.
> >> >"Gord Dibben" wrote:
> >> >> One password per sheet.
> >> >> You can get around this either of two ways but I'll add the warning that Excel's > >> >> internal security is quite weak and passwords can be broken.
> >> >> 1. Have workbook_open code that checks to see who is the log-in user and open > >> >> just that sheet with all others veryhidden or a blank(dummy) sheet if users > >> >> disable macros.
> >> >> 2. Each user has his own password. The workbook opens with a blank(dummy) sheet > >> >> and an InputBox for user to enter his password which opens his sheet only.
> >> >> No password to open the workbook, just code to make a user's sheet visible.
> >> >> In the Thisworkbook Module....................
> >> >> Private Sub Workbook_Open() > >> >> Dim pword As String > >> >> Select Case Environ("Username") > >> >> 'if a login is not used and each user has his own sheet password change to > >> >> 'pword = InputBox("Enter Your Password") > >> >> 'Select Case pword > >> >> Case Is = "Gord": Sheets("Gordsheet").Visible = True > >> >> Sheets("Dummy").Visible = False > >> >> Case Is = "Pete": Sheets("Petesheet").Visible = True > >> >> Sheets("Dummy").Visible = False
> >> >> End Select > >> >> End Sub
> >> >> Private Sub Workbook_BeforeClose(Cancel As Boolean) > >> >> Dim sht As Worksheet > >> >> Application.ScreenUpdating = False > >> >> Sheets("Dummy").Visible = xlSheetVisible > >> >> For Each sht In ActiveWorkbook.Sheets > >> >> If sht.Name <> "Dummy" Then > >> >> sht.Visible = xlSheetVeryHidden > >> >> End If > >> >> Next sht > >> >> Application.ScreenUpdating = True > >> >> ThisWorkbook.Save > >> >> End Sub
> >> >> You would protect the project so's prying eyes cannot see the code and > >> >> passwords.
> >> >> You may also want as administrator to be able to open all sheets for editing.
> >> >> Add these lines to the Workbook_Open code above End Select
> >> >> Case Is = "Admin" > >> >> For n = 1 To Sheets.Count > >> >> Sheets(n).Visible = True > >> >> Next n
> >> >> Gord Dibben MS Excel MVP
> >> >> On Tue, 27 Nov 2007 11:15:02 -0800, Kristi <Kri...@discussions.microsoft.com> > >> >> wrote:
> >> >> >I have a shared document that needs to be set up so that individuals can > >> >> >access only the worksheet for them (no others) as well as protected cells > >> >> >within that worksheet. I have set the passwords for the sheets as far as the > >> >> >data is concerned, but is it possible to protect each worksheet with an > >> >> >additional password or can only one password be used per worksheet? The > >> >> >trouble is that one individual could/would enter data on anothers worksheet.
An update on this............Kristi mailed me the workbook and we got it set up using the method of hiding sheets except for "Dummy" and user enters a password to unhide just the user sheet.
>>Kristi, >>I was going to offer to have you send workbook to me to look over, but Gord >>has beaten me to that offer, and I think to keep confusion to you to a >>minimum it's probably best to work with just one mentor - in this case, I'll >>defer to Gord, so go ahead and get in touch with him. I can't imagine either >>of you needing me from this point, even though he doesn't sell insurance, >>"You're in good hands with Gord" <g>, but if you do, just reply to this and >>I'll be back.
>>"Kristi" wrote:
>>> Nothing happened. I'm sure I am not doing something right, I just don't know >>> what.
>>> "JLatham" wrote:
>>> > To put code in a worksheet's event handlers, right-click on the worksheet's >>> > name tab and choose View Code from the popup list. You can choose Worksheet >>> > from the left hand pull down at the top, and which event from the right hand >>> > pull down list. You'll have a 'stub' for _SelectionChange() which you can >>> > delete. In the case of the code I pointed to, you wouldn't need the >>> > pull-downs, just copy and paste the code into that module. Note that you >>> > have to do this for each worksheet, and of course modify the code to use the >>> > appropriate password for it.
>>> > To get to where you need to be for the solution that Gord has offered, >>> > right-click on the Excel icon immediately to the left of the word "File" in >>> > the main Excel menu toolbar and again, choose [View Code] from that popup >>> > list and copy/paste his code if you choose to go that route.
>>> > "Kristi" wrote:
>>> > > How/where do I insert the code on the separate worksheets? Can it be the >>> > > same password as the data locked password?
>>> > > "JLatham" wrote:
>>> > > > Each worksheet can have a unique password. All you have to do is assign that >>> > > > password to the sheet. Of course you also have to remember what the password >>> > > > is for each of the sheets.
>>> > > > As long as the people using the workbook are 'honest' and all you're >>> > > > interested in doing is keeping users from accidentally altering someone >>> > > > else's data or using the wrong sheet, then things are fine. But remember >>> > > > that the encryption of the password for both workbook and worksheet >>> > > > protection is very weak and easily cracked - so it's not much for providing >>> > > > true 'security' type protection.
>>> > > > You could put code in each worksheet's _Activate() event to prompt for its >>> > > > password, and if the proper password isn't given, leave the sheet locked up. >>> > > > Another person posting a question about this kind of thing had such a deal >>> > > > set up: when you activated a sheet, it first hid all of the columns on that >>> > > > sheet and requested the password. If the proper password wasn't given, then >>> > > > the columns remained hidden (and the sheet remained protected), but if the >>> > > > proper password was given, then the columns were made visible and sheet >>> > > > protection was removed. The sheet's _Deactivate() event handler was set up >>> > > > to always put the sheet back into protected state with the columns hidden >>> > > > again.
>>> > > > > I have a shared document that needs to be set up so that individuals can >>> > > > > access only the worksheet for them (no others) as well as protected cells >>> > > > > within that worksheet. I have set the passwords for the sheets as far as the >>> > > > > data is concerned, but is it possible to protect each worksheet with an >>> > > > > additional password or can only one password be used per worksheet? The >>> > > > > trouble is that one individual could/would enter data on anothers worksheet.
> An update on this............Kristi mailed me the workbook and we got it set up > using the method of hiding sheets except for "Dummy" and user enters a password > to unhide just the user sheet.
> Kristi was pleased with this.
> Gord
> On Tue, 27 Nov 2007 16:14:40 -0800, Gord Dibben <gorddibbATshawDOTca> wrote:
> >>Kristi, > >>I was going to offer to have you send workbook to me to look over, but Gord > >>has beaten me to that offer, and I think to keep confusion to you to a > >>minimum it's probably best to work with just one mentor - in this case, I'll > >>defer to Gord, so go ahead and get in touch with him. I can't imagine either > >>of you needing me from this point, even though he doesn't sell insurance, > >>"You're in good hands with Gord" <g>, but if you do, just reply to this and > >>I'll be back.
> >>"Kristi" wrote:
> >>> Nothing happened. I'm sure I am not doing something right, I just don't know > >>> what.
> >>> "JLatham" wrote:
> >>> > To put code in a worksheet's event handlers, right-click on the worksheet's > >>> > name tab and choose View Code from the popup list. You can choose Worksheet > >>> > from the left hand pull down at the top, and which event from the right hand > >>> > pull down list. You'll have a 'stub' for _SelectionChange() which you can > >>> > delete. In the case of the code I pointed to, you wouldn't need the > >>> > pull-downs, just copy and paste the code into that module. Note that you > >>> > have to do this for each worksheet, and of course modify the code to use the > >>> > appropriate password for it.
> >>> > To get to where you need to be for the solution that Gord has offered, > >>> > right-click on the Excel icon immediately to the left of the word "File" in > >>> > the main Excel menu toolbar and again, choose [View Code] from that popup > >>> > list and copy/paste his code if you choose to go that route.
> >>> > "Kristi" wrote:
> >>> > > How/where do I insert the code on the separate worksheets? Can it be the > >>> > > same password as the data locked password?
> >>> > > "JLatham" wrote:
> >>> > > > Each worksheet can have a unique password. All you have to do is assign that > >>> > > > password to the sheet. Of course you also have to remember what the password > >>> > > > is for each of the sheets.
> >>> > > > As long as the people using the workbook are 'honest' and all you're > >>> > > > interested in doing is keeping users from accidentally altering someone > >>> > > > else's data or using the wrong sheet, then things are fine. But remember > >>> > > > that the encryption of the password for both workbook and worksheet > >>> > > > protection is very weak and easily cracked - so it's not much for providing > >>> > > > true 'security' type protection.
> >>> > > > You could put code in each worksheet's _Activate() event to prompt for its > >>> > > > password, and if the proper password isn't given, leave the sheet locked up. > >>> > > > Another person posting a question about this kind of thing had such a deal > >>> > > > set up: when you activated a sheet, it first hid all of the columns on that > >>> > > > sheet and requested the password. If the proper password wasn't given, then > >>> > > > the columns remained hidden (and the sheet remained protected), but if the > >>> > > > proper password was given, then the columns were made visible and sheet > >>> > > > protection was removed. The sheet's _Deactivate() event handler was set up > >>> > > > to always put the sheet back into protected state with the columns hidden > >>> > > > again.
> >>> > > > > I have a shared document that needs to be set up so that individuals can > >>> > > > > access only the worksheet for them (no others) as well as protected cells > >>> > > > > within that worksheet. I have set the passwords for the sheets as far as the > >>> > > > > data is concerned, but is it possible to protect each worksheet with an > >>> > > > > additional password or can only one password be used per worksheet? The > >>> > > > > trouble is that one individual could/would enter data on anothers worksheet.
