Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to limit access to sheets ?? HELP ME
greet all,
We will suppose, that I work in the firm which be divided on several sections. Example: DI - programmer LT - Constructors SD - purchases FICO - finances and kontroling Is about 10 sections. I want to create file in Excel, they to which all with exchanged setions will have access, but in limited method. I want, to every of section had access to his sheet only. It can't open different sheets. This very important. The names of sheets will be the same as the name of sections. Every of sheet (every section) should be protected by password. I would want, to every user before open the file should be asked about password. If it will write correctly ( will be 3 trial), suitable sheet will open. If wrote incorrec file will close. How to the best administer with passwords Write it in code VBA ?? Maybe someone has different idea ? Regards, Andrzej |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to limit access to sheets ?? HELP ME
You'll need to find a different solution.
If it's important that individuals can only open "their" sheet(s), Excel has nothing that will prevent someone savvy enough to find these newsgroups from bypassing any internal protection. Especially since you're presumably dealing with people with at least moderate technical ability. See http://www.mcgimpsey.com/excel/removepwords.html and http://www.mcgimpsey.com/excel/fileandvbapwords.html In article , And1 wrote: I want, to every of section had access to his sheet only. It can't open different sheets. This very important. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to limit access to sheets ?? HELP ME
Well you are right, but i need something like this do
but, If you can me help answer me. If someone can me help, Please answer me. Regards, Andrzej JE McGimpsey napisa³(a): You'll need to find a different solution. If it's important that individuals can only open "their" sheet(s), Excel has nothing that will prevent someone savvy enough to find these newsgroups from bypassing any internal protection. Especially since you're presumably dealing with people with at least moderate technical ability. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to limit access to sheets ?? HELP ME
If you change your question to:
I need some help making it so individuals can see their sheet easily, but it's ok for everyone to see the other sheets if they know how and once they see it, they can share that info with anyone they want, then you may get some more help. But I'd be very careful putting finances in a workbook that I'm sharing with people who shouldn't see it. And1 wrote: Well you are right, but i need something like this do but, If you can me help answer me. If someone can me help, Please answer me. Regards, Andrzej JE McGimpsey napisa³(a): You'll need to find a different solution. If it's important that individuals can only open "their" sheet(s), Excel has nothing that will prevent someone savvy enough to find these newsgroups from bypassing any internal protection. Especially since you're presumably dealing with people with at least moderate technical ability. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to limit access to sheets ?? HELP ME
I understand this.
but, where you know, that information in these sheets will be about secret financial ? I will get in there information about waste. Example: How many given department used up paper...:) I think to use propriety VISIBLE for Sheet. I don't know only how to solve with password problem. To write in code of programme...maybe in separate hidden sheet. Now, someone help me ? Regards, Andrzej Dave Peterson napisał(a): If you change your question to: I need some help making it so individuals can see their sheet easily, but it's ok for everyone to see the other sheets if they know how and once they see it, they can share that info with anyone they want, then you may get some more help. But I'd be very careful putting finances in a workbook that I'm sharing with people who shouldn't see it. And1 wrote: Well you are right, but i need something like this do but, If you can me help answer me. If someone can me help, Please answer me. Regards, Andrzej JE McGimpsey napisa³(a): You'll need to find a different solution. If it's important that individuals can only open "their" sheet(s), Excel has nothing that will prevent someone savvy enough to find these newsgroups from bypassing any internal protection. Especially since you're presumably dealing with people with at least moderate technical ability. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to limit access to sheets ?? HELP ME
If macros are disabled, then this will not work!
But you could base your access on the user's name (Tools|Options|General tab). I created a worksheet named List that had the usernames in column A and the sheet that they can see in column B. Then I put this in a general module: Option Explicit Sub auto_open() Dim wks As Worksheet Dim ListWks As Worksheet Dim InstWks As Worksheet Dim res As Variant Set InstWks = ThisWorkbook.Worksheets("Instructions") Set ListWks = ThisWorkbook.Worksheets("List") InstWks.Visible = xlSheetVisible For Each wks In ThisWorkbook.Worksheets If wks.Name < InstWks.Name Then wks.Visible = xlSheetHidden End If Next wks res = Application.Match(Application.UserName, ListWks.Range("a:a"), 0) If IsError(res) Then MsgBox "not authorized for anything!" Exit Sub Else ThisWorkbook.Worksheets _ (ListWks.Range("b:b").Cells(res, 1).Value).Visible = xlSheetVisible End If End Sub All this fails with macros disabled and won't stop anyone from viewing any sheet that they can. And1 wrote: I understand this. but, where you know, that information in these sheets will be about secret financial ? I will get in there information about waste. Example: How many given department used up paper...:) I think to use propriety VISIBLE for Sheet. I don't know only how to solve with password problem. To write in code of programme...maybe in separate hidden sheet. Now, someone help me ? Regards, Andrzej Dave Peterson napisał(a): If you change your question to: I need some help making it so individuals can see their sheet easily, but it's ok for everyone to see the other sheets if they know how and once they see it, they can share that info with anyone they want, then you may get some more help. But I'd be very careful putting finances in a workbook that I'm sharing with people who shouldn't see it. And1 wrote: Well you are right, but i need something like this do but, If you can me help answer me. If someone can me help, Please answer me. Regards, Andrzej JE McGimpsey napisa³(a): You'll need to find a different solution. If it's important that individuals can only open "their" sheet(s), Excel has nothing that will prevent someone savvy enough to find these newsgroups from bypassing any internal protection. Especially since you're presumably dealing with people with at least moderate technical ability. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to limit access to sheets ?? HELP ME
OK. but
in case of don't of authorization, active (and visible) will be the sheet("Instructions") And what will be if I change name from "Instructions" on different (exemple "Andrew" - my name) and I record change??? Next time will not work. I would like to if in case of don't of authorization don't active (enabled) any sheets. The best to close workbook. So I created "List" as you. And created "Instructions", but for what it was needed sheets("Instructions") I wrote that code: Private Sub Workbook_Open() Dim wks As Worksheet Dim ListWks As Worksheet Dim InstWks As Worksheet Dim res As Variant Set InstWks = ThisWorkbook.Worksheets("Instructions") Set ListWks = ThisWorkbook.Worksheets("List") InstWks.Visible = xlSheetVisible For Each wks In ThisWorkbook.Worksheets If wks.Name < InstWks.Name Then wks.Visible = xlSheetHidden End If Next wks res = Application.Match(Application.UserName, ListWks.Range("a:a"),0) If IsError(res) Then MsgBox "You don't authorized for anything!" ' What HERE WRITE??????? Exit Sub Else ThisWorkbook.Worksheets _ (ListWks.Range("b:b").Cells(res, 1).Value).Visible = xlSheetVisible ThisWorkbook.Worksheets _ (ListWks.Range("b:b").Cells(res, 1).Value).Activate End If End Sub Regards, Andrzej Dave Peterson napisaÅ‚(a): If macros are disabled, then this will not work! But you could base your access on the user's name (Tools|Options|General tab). I created a worksheet named List that had the usernames in column A and the sheet that they can see in column B. Then I put this in a general module: Option Explicit Sub auto_open() Dim wks As Worksheet Dim ListWks As Worksheet Dim InstWks As Worksheet Dim res As Variant Set InstWks = ThisWorkbook.Worksheets("Instructions") Set ListWks = ThisWorkbook.Worksheets("List") InstWks.Visible = xlSheetVisible For Each wks In ThisWorkbook.Worksheets If wks.Name < InstWks.Name Then wks.Visible = xlSheetHidden End If Next wks res = Application.Match(Application.UserName, ListWks.Range("a:a"), 0) If IsError(res) Then MsgBox "not authorized for anything!" Exit Sub Else ThisWorkbook.Worksheets _ (ListWks.Range("b:b").Cells(res, 1).Value).Visible = xlSheetVisible End If End Sub All this fails with macros disabled and won't stop anyone from viewing any sheet that they can. And1 wrote: I understand this. but, where you know, that information in these sheets will be about secret financial ? I will get in there information about waste. Example: How many given department used up paper...:) I think to use propriety VISIBLE for Sheet. I don't know only how to solve with password problem. To write in code of programme...maybe in separate hidden sheet. Now, someone help me ? Regards, Andrzej Dave Peterson napisaÃ…€š(a): If you change your question to: I need some help making it so individuals can see their sheet easily, but it's ok for everyone to see the other sheets if they know how and once they see it, they can share that info with anyone they want, then you may get some more help. But I'd be very careful putting finances in a workbook that I'm sharing with people who shouldn't see it. And1 wrote: Well you are right, but i need something like this do but, If you can me help answer me. If someone can me help, Please answer me. Regards, Andrzej JE McGimpsey napisa³(a): You'll need to find a different solution. If it's important that individuals can only open "their" sheet(s), Excel has nothing that will prevent someone savvy enough to find these newsgroups from bypassing any internal protection. Especially since you're presumably dealing with people with at least moderate technical ability. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to limit access to sheets ?? HELP ME
If you change names, then the code won't work. You could use codenames for the
worksheets, though. This Set InstWks = ThisWorkbook.Worksheets("Instructions") Set ListWks = ThisWorkbook.Worksheets("List") becomes Set InstWks = Sheet1 Set ListWks = Sheet2 Next time you're in the VBE, select your project. Click on the worksheet that is named List (or Instructions). Hit F4 to see its properties. Notice the (name) property at the top. Use that. and this: If IsError(res) Then MsgBox "not authorized for anything!" Exit Sub Else becomes If IsError(res) Then MsgBox "not authorized for anything!" thisworkbook.close savechanges:=false Exit Sub Else And1 wrote: OK. but in case of don't of authorization, active (and visible) will be the sheet("Instructions") And what will be if I change name from "Instructions" on different (exemple "Andrew" - my name) and I record change??? Next time will not work. I would like to if in case of don't of authorization don't active (enabled) any sheets. The best to close workbook. So I created "List" as you. And created "Instructions", but for what it was needed sheets("Instructions") I wrote that code: Private Sub Workbook_Open() Dim wks As Worksheet Dim ListWks As Worksheet Dim InstWks As Worksheet Dim res As Variant Set InstWks = ThisWorkbook.Worksheets("Instructions") Set ListWks = ThisWorkbook.Worksheets("List") InstWks.Visible = xlSheetVisible For Each wks In ThisWorkbook.Worksheets If wks.Name < InstWks.Name Then wks.Visible = xlSheetHidden End If Next wks res = Application.Match(Application.UserName, ListWks.Range("a:a"),0) If IsError(res) Then MsgBox "You don't authorized for anything!" ' What HERE WRITE??????? Exit Sub Else ThisWorkbook.Worksheets _ (ListWks.Range("b:b").Cells(res, 1).Value).Visible = xlSheetVisible ThisWorkbook.Worksheets _ (ListWks.Range("b:b").Cells(res, 1).Value).Activate End If End Sub Regards, Andrzej Dave Peterson napisaÅ‚(a): If macros are disabled, then this will not work! But you could base your access on the user's name (Tools|Options|General tab). I created a worksheet named List that had the usernames in column A and the sheet that they can see in column B. Then I put this in a general module: Option Explicit Sub auto_open() Dim wks As Worksheet Dim ListWks As Worksheet Dim InstWks As Worksheet Dim res As Variant Set InstWks = ThisWorkbook.Worksheets("Instructions") Set ListWks = ThisWorkbook.Worksheets("List") InstWks.Visible = xlSheetVisible For Each wks In ThisWorkbook.Worksheets If wks.Name < InstWks.Name Then wks.Visible = xlSheetHidden End If Next wks res = Application.Match(Application.UserName, ListWks.Range("a:a"), 0) If IsError(res) Then MsgBox "not authorized for anything!" Exit Sub Else ThisWorkbook.Worksheets _ (ListWks.Range("b:b").Cells(res, 1).Value).Visible = xlSheetVisible End If End Sub All this fails with macros disabled and won't stop anyone from viewing any sheet that they can. And1 wrote: I understand this. but, where you know, that information in these sheets will be about secret financial ? I will get in there information about waste. Example: How many given department used up paper...:) I think to use propriety VISIBLE for Sheet. I don't know only how to solve with password problem. To write in code of programme...maybe in separate hidden sheet. Now, someone help me ? Regards, Andrzej Dave Peterson napisaÃ…€š(a): If you change your question to: I need some help making it so individuals can see their sheet easily, but it's ok for everyone to see the other sheets if they know how and once they see it, they can share that info with anyone they want, then you may get some more help. But I'd be very careful putting finances in a workbook that I'm sharing with people who shouldn't see it. And1 wrote: Well you are right, but i need something like this do but, If you can me help answer me. If someone can me help, Please answer me. Regards, Andrzej JE McGimpsey napisa³(a): You'll need to find a different solution. If it's important that individuals can only open "their" sheet(s), Excel has nothing that will prevent someone savvy enough to find these newsgroups from bypassing any internal protection. Especially since you're presumably dealing with people with at least moderate technical ability. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limit access to range of cells | Excel Worksheet Functions | |||
Limit access in a pivot table | Excel Worksheet Functions | |||
Limit access to certain worksheets (tabs) | Excel Worksheet Functions | |||
Limit access to certain worksheets (tabs) | Excel Discussion (Misc queries) | |||
Limit access to certain worksheets (tabs) | Excel Programming |