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

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.

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.

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

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.

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

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.

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