Hi

I am populating a sales template in one Excel workbook but I want business
unit managers to have access to view their respective worksheet only €¦ I want
to set a restriction, maybe by using password.
So, each manager will be given a password and they need to enter it when
opening the file, then they can only view what is within their own worksheet.

Is it possible in Excel to do so?

Please help....

--
exalan

First, Excel does not offer offer a significant level of protection. The
workbook/worksheet passwords can be cracked via macros in about 2-5 minutes.
Or, the managers could simple write a formula that references a hidden sheet
to find out what is stored there!

If you're going more for the conveniece, you could setup a macro that based
on:
Environ ("UserName")

would then decide which sheet to display. But again, if this information is
sensitive at all, your best bet is to place the data in seperate workbooks.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"exalan" wrote:

Hi

I am populating a sales template in one Excel workbook but I want business
unit managers to have access to view their respective worksheet only €¦ I want
to set a restriction, maybe by using password.
So, each manager will be given a password and they need to enter it when
opening the file, then they can only view what is within their own worksheet.

Is it possible in Excel to do so?

Please help....

--
exalan

Hi,
in the worksheet name for each manager right click, then paste the code as
follow, you will have to change the passwword, you will see three places with
the password, in the example below MANAGER. so then you hide the worksheet.
When opening a popup will show up asking for the password

if this helps please click yes thanks


Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to access DATA sheet")

If strPassword = "" Then
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
ElseIf strPassword < "MANAGER" Then
MsgBox "Password Incorrect "
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If
Range("a1").Select

On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True
On Error GoTo 0
End Sub

"exalan" wrote:

Hi

I am populating a sales template in one Excel workbook but I want business
unit managers to have access to view their respective worksheet only €¦ I want
to set a restriction, maybe by using password.
So, each manager will be given a password and they need to enter it when
opening the file, then they can only view what is within their own worksheet.

Is it possible in Excel to do so?

Please help....

--
exalan

You could try, but you're doomed for failure if anyone is really interested.

Excel's security isn't meant for this kind of thing.

Maybe it's time for a separate workbook for each manager.

exalan wrote:

Hi

I am populating a sales template in one Excel workbook but I want business
unit managers to have access to view their respective worksheet only €¦ I want
to set a restriction, maybe by using password.
So, each manager will be given a password and they need to enter it when
opening the file, then they can only view what is within their own worksheet.

Is it possible in Excel to do so?

Please help....

--
exalan

--

Dave Peterson

Hi Eduardo

Thanks for the advice.

I've created 3 worksheets "Alan", "Adrian" & "Andrew" and copy & paste the
macros and added the respective passwords as you've guided. However, I can
only open the 2nd and 3rd worksheets. As for the 1st worksheet ("Alan"), it
can open but the screen is locked (blueish screen).

Appreciate if you can investigate and advise.

Best regards

--
exalan


"Eduardo" wrote:

Hi,
in the worksheet name for each manager right click, then paste the code as
follow, you will have to change the passwword, you will see three places with
the password, in the example below MANAGER. so then you hide the worksheet.
When opening a popup will show up asking for the password

if this helps please click yes thanks


Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to access DATA sheet")

If strPassword = "" Then
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
ElseIf strPassword < "MANAGER" Then
MsgBox "Password Incorrect "
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If
Range("a1").Select

On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True
On Error GoTo 0
End Sub

"exalan" wrote:

Hi

I am populating a sales template in one Excel workbook but I want business
unit managers to have access to view their respective worksheet only €¦ I want
to set a restriction, maybe by using password.
So, each manager will be given a password and they need to enter it when
opening the file, then they can only view what is within their own worksheet.

Is it possible in Excel to do so?

Please help....

--
exalan

Hi Luke

Thanks for your advice.
Best regards
--
exalan


"Luke M" wrote:

First, Excel does not offer offer a significant level of protection. The
workbook/worksheet passwords can be cracked via macros in about 2-5 minutes.
Or, the managers could simple write a formula that references a hidden sheet
to find out what is stored there!

If you're going more for the conveniece, you could setup a macro that based
on:
Environ ("UserName")

would then decide which sheet to display. But again, if this information is
sensitive at all, your best bet is to place the data in seperate workbooks.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"exalan" wrote:

Hi

I am populating a sales template in one Excel workbook but I want business
unit managers to have access to view their respective worksheet only €¦ I want
to set a restriction, maybe by using password.
So, each manager will be given a password and they need to enter it when
opening the file, then they can only view what is within their own worksheet.

Is it possible in Excel to do so?

Please help....

--
exalan

Hi Dave

Thanks for your advice.
Best regards
--
exalan


"Dave Peterson" wrote:

You could try, but you're doomed for failure if anyone is really interested.

Excel's security isn't meant for this kind of thing.

Maybe it's time for a separate workbook for each manager.

exalan wrote:

Hi

I am populating a sales template in one Excel workbook but I want business
unit managers to have access to view their respective worksheet only €¦ I want
to set a restriction, maybe by using password.
So, each manager will be given a password and they need to enter it when
opening the file, then they can only view what is within their own worksheet.

Is it possible in Excel to do so?

Please help....

--
exalan

--

Dave Peterson
.

Hi,
That macro I gave to you is working for me, I have more than 20 sheets, do
something hide all the sheets and then try to open Alan, do something else
include a menu with a buttom called Alan and add this code which will unhide
the sheet to see what happens. Call the sheet where you have the button Menu,
and run the button from there, macro will see into that sheet first

Sub GSG_Target()
'
' GSG_Target Macro
'

'
Sheets("Menu").Select
Sheets("GSG - Alan").Visible = True
Sheets("GSG - Alan").Select

"exalan" wrote:

Hi Eduardo

Thanks for the advice.

I've created 3 worksheets "Alan", "Adrian" & "Andrew" and copy & paste the
macros and added the respective passwords as you've guided. However, I can
only open the 2nd and 3rd worksheets. As for the 1st worksheet ("Alan"), it
can open but the screen is locked (blueish screen).

Appreciate if you can investigate and advise.

Best regards

--
exalan


"Eduardo" wrote:

Hi,
in the worksheet name for each manager right click, then paste the code as
follow, you will have to change the passwword, you will see three places with
the password, in the example below MANAGER. so then you hide the worksheet.
When opening a popup will show up asking for the password

if this helps please click yes thanks


Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to access DATA sheet")

If strPassword = "" Then
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
ElseIf strPassword < "MANAGER" Then
MsgBox "Password Incorrect "
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If
Range("a1").Select

On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True
On Error GoTo 0
End Sub

"exalan" wrote:

Hi

I am populating a sales template in one Excel workbook but I want business
unit managers to have access to view their respective worksheet only €¦ I want
to set a restriction, maybe by using password.
So, each manager will be given a password and they need to enter it when
opening the file, then they can only view what is within their own worksheet.

Is it possible in Excel to do so?

Please help....

--
exalan

Hi Eduardo

Many thanks for the tips. it works!
--
exalan


"Eduardo" wrote:

Hi,
That macro I gave to you is working for me, I have more than 20 sheets, do
something hide all the sheets and then try to open Alan, do something else
include a menu with a buttom called Alan and add this code which will unhide
the sheet to see what happens. Call the sheet where you have the button Menu,
and run the button from there, macro will see into that sheet first

Sub GSG_Target()
'
' GSG_Target Macro
'

'
Sheets("Menu").Select
Sheets("GSG - Alan").Visible = True
Sheets("GSG - Alan").Select

"exalan" wrote:

Hi Eduardo

Thanks for the advice.

I've created 3 worksheets "Alan", "Adrian" & "Andrew" and copy & paste the
macros and added the respective passwords as you've guided. However, I can
only open the 2nd and 3rd worksheets. As for the 1st worksheet ("Alan"), it
can open but the screen is locked (blueish screen).

Appreciate if you can investigate and advise.

Best regards

--
exalan


"Eduardo" wrote:

Hi,
in the worksheet name for each manager right click, then paste the code as
follow, you will have to change the passwword, you will see three places with
the password, in the example below MANAGER. so then you hide the worksheet.
When opening a popup will show up asking for the password

if this helps please click yes thanks


Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to access DATA sheet")

If strPassword = "" Then
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
ElseIf strPassword < "MANAGER" Then
MsgBox "Password Incorrect "
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If
Range("a1").Select

On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True
On Error GoTo 0
End Sub

"exalan" wrote:

Hi

I am populating a sales template in one Excel workbook but I want business
unit managers to have access to view their respective worksheet only €¦ I want
to set a restriction, maybe by using password.
So, each manager will be given a password and they need to enter it when
opening the file, then they can only view what is within their own worksheet.

Is it possible in Excel to do so?

Please help....

--
exalan