Protecting Sheet to Prevent Viewing At All

Is it possible to protect a sheet in a workbook so that it isn't even
viewable unless you have the password? One of the sheets in my workbook
contaiins highly sensitive information that I don't want most folks to see,
but I want users to be continue to hide and unhide other elements in the
workbook to fit their needs.

Is there something I can write in VBA that will do the trick? In the perfect
world, what would happen is that, when a user clicked on the tab for the
Sensitive Sheet, a popup box would appear asking for a password. Is this even
possible?

Help! Any answers would be much appreciated.

Cheers!

The following code will make the sheet 'invisible' to ordinary users:

Sub hideit()
Sheets("Sheet2").Visible = xlVeryHidden
End Sub

To unhide it use

Sub unhideit()
Sheets("Sheet2").Visible = True
End Sub


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200507/1

Both this and the other reply, below, will be phenomenally useful. Thank you
both! The Excel rescue team comes through again!

"VoG" wrote:

> The following code will make the sheet 'invisible' to ordinary users:
>
> Sub hideit()
> Sheets("Sheet2").Visible = xlVeryHidden
> End Sub
>
> To unhide it use
>
> Sub unhideit()
> Sheets("Sheet2").Visible = True
> End Sub
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200507/1
>

Right Click the Sheet tab and select "View Code" then add
the following code. You can change the password to whatever you like.


Private Const Password As String = "DoubleOhSeven"

Private Sub Worksheet_Activate()
Me.Visible = False
If InputBox("Please Enter the Password for the " & Me.Name & " Sheet",
"Enter Password") <> Password Then
Me.Visible = False
Else
Me.Visible = True
End If
End Sub


"Michael Link" wrote:

> Is it possible to protect a sheet in a workbook so that it isn't even
> viewable unless you have the password? One of the sheets in my workbook
> contaiins highly sensitive information that I don't want most folks to see,
> but I want users to be continue to hide and unhide other elements in the
> workbook to fit their needs.
>
> Is there something I can write in VBA that will do the trick? In the perfect
> world, what would happen is that, when a user clicked on the tab for the
> Sensitive Sheet, a popup box would appear asking for a password. Is this even
> possible?
>
> Help! Any answers would be much appreciated.
>
> Cheers!
>
>

As Mr. Burns would say, EXCELLENT! This will be very helpful. Thank you so
much!

"Hayeso" wrote:

> Right Click the Sheet tab and select "View Code" then add
> the following code. You can change the password to whatever you like.
>
>
> Private Const Password As String = "DoubleOhSeven"
>
> Private Sub Worksheet_Activate()
> Me.Visible = False
> If InputBox("Please Enter the Password for the " & Me.Name & " Sheet",
> "Enter Password") <> Password Then
> Me.Visible = False
> Else
> Me.Visible = True
> End If
> End Sub
>
>
> "Michael Link" wrote:
>
> > Is it possible to protect a sheet in a workbook so that it isn't even
> > viewable unless you have the password? One of the sheets in my workbook
> > contaiins highly sensitive information that I don't want most folks to see,
> > but I want users to be continue to hide and unhide other elements in the
> > workbook to fit their needs.
> >
> > Is there something I can write in VBA that will do the trick? In the perfect
> > world, what would happen is that, when a user clicked on the tab for the
> > Sensitive Sheet, a popup box would appear asking for a password. Is this even
> > possible?
> >
> > Help! Any answers would be much appreciated.
> >
> > Cheers!
> >
> >

Hi--

Actually, I just had a few minutes to try to implement this. Unfortunately,
I'm gettiing a "Syntax Error" message, indicating that there's an
end-of-statement issue. Might you have any ideas what the issue is?

Thanks!

"Hayeso" wrote:

> Right Click the Sheet tab and select "View Code" then add
> the following code. You can change the password to whatever you like.
>
>
> Private Const Password As String = "DoubleOhSeven"
>
> Private Sub Worksheet_Activate()
> Me.Visible = False
> If InputBox("Please Enter the Password for the " & Me.Name & " Sheet",
> "Enter Password") <> Password Then
> Me.Visible = False
> Else
> Me.Visible = True
> End If
> End Sub
>
>
> "Michael Link" wrote:
>
> > Is it possible to protect a sheet in a workbook so that it isn't even
> > viewable unless you have the password? One of the sheets in my workbook
> > contaiins highly sensitive information that I don't want most folks to see,
> > but I want users to be continue to hide and unhide other elements in the
> > workbook to fit their needs.
> >
> > Is there something I can write in VBA that will do the trick? In the perfect
> > world, what would happen is that, when a user clicked on the tab for the
> > Sensitive Sheet, a popup box would appear asking for a password. Is this even
> > possible?
> >
> > Help! Any answers would be much appreciated.
> >
> > Cheers!
> >
> >

Hi Michael,

one line of Hayeso's code has wrapped making it appear as two lines. To
obviate the problem, replace:

> If InputBox("Please Enter the Password for the " & Me.Name & " Sheet",
> "Enter Password") <> Password Then

with:

If InputBox("Please Enter the Password for the " _
& Me.Name & " Sheet", "Enter Password") _
<> Password Then

---
Regards,
Norman



"Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
news:1F99BB20-811D-4A1E-8318-7DE53CB6AE03@microsoft.com...
> Hi--
>
> Actually, I just had a few minutes to try to implement this.
> Unfortunately,
> I'm gettiing a "Syntax Error" message, indicating that there's an
> end-of-statement issue. Might you have any ideas what the issue is?
>
> Thanks!
>
> "Hayeso" wrote:
>
>> Right Click the Sheet tab and select "View Code" then add
>> the following code. You can change the password to whatever you like.
>>
>>
>> Private Const Password As String = "DoubleOhSeven"
>>
>> Private Sub Worksheet_Activate()
>> Me.Visible = False
>> If InputBox("Please Enter the Password for the " & Me.Name & "
>> Sheet",
>> "Enter Password") <> Password Then
>> Me.Visible = False
>> Else
>> Me.Visible = True
>> End If
>> End Sub
>>
>>
>> "Michael Link" wrote:
>>
>> > Is it possible to protect a sheet in a workbook so that it isn't even
>> > viewable unless you have the password? One of the sheets in my workbook
>> > contaiins highly sensitive information that I don't want most folks to
>> > see,
>> > but I want users to be continue to hide and unhide other elements in
>> > the
>> > workbook to fit their needs.
>> >
>> > Is there something I can write in VBA that will do the trick? In the
>> > perfect
>> > world, what would happen is that, when a user clicked on the tab for
>> > the
>> > Sensitive Sheet, a popup box would appear asking for a password. Is
>> > this even
>> > possible?
>> >
>> > Help! Any answers would be much appreciated.
>> >
>> > Cheers!
>> >
>> >

Hi Norman,

Thanks for the help! I also required some information to be restricted. Out
of curiosity after unhiding the cell, how do you go in at a later time to
change the password? When I click on the sheet it continues to bring the
password screen up over and over. Is there a way to select on the tab and
click view code again?

Sincerely,

Dave

"Norman Jones" wrote:

> Hi Michael,
>
> one line of Hayeso's code has wrapped making it appear as two lines. To
> obviate the problem, replace:
>
> > If InputBox("Please Enter the Password for the " & Me.Name & " Sheet",
> > "Enter Password") <> Password Then
>
> with:
>
> If InputBox("Please Enter the Password for the " _
> & Me.Name & " Sheet", "Enter Password") _
> <> Password Then
>
> ---
> Regards,
> Norman
>
>
>
> "Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
> news:1F99BB20-811D-4A1E-8318-7DE53CB6AE03@microsoft.com...
> > Hi--
> >
> > Actually, I just had a few minutes to try to implement this.
> > Unfortunately,
> > I'm gettiing a "Syntax Error" message, indicating that there's an
> > end-of-statement issue. Might you have any ideas what the issue is?
> >
> > Thanks!
> >
> > "Hayeso" wrote:
> >
> >> Right Click the Sheet tab and select "View Code" then add
> >> the following code. You can change the password to whatever you like.
> >>
> >>
> >> Private Const Password As String = "DoubleOhSeven"
> >>
> >> Private Sub Worksheet_Activate()
> >> Me.Visible = False
> >> If InputBox("Please Enter the Password for the " & Me.Name & "
> >> Sheet",
> >> "Enter Password") <> Password Then
> >> Me.Visible = False
> >> Else
> >> Me.Visible = True
> >> End If
> >> End Sub
> >>
> >>
> >> "Michael Link" wrote:
> >>
> >> > Is it possible to protect a sheet in a workbook so that it isn't even
> >> > viewable unless you have the password? One of the sheets in my workbook
> >> > contaiins highly sensitive information that I don't want most folks to
> >> > see,
> >> > but I want users to be continue to hide and unhide other elements in
> >> > the
> >> > workbook to fit their needs.
> >> >
> >> > Is there something I can write in VBA that will do the trick? In the
> >> > perfect
> >> > world, what would happen is that, when a user clicked on the tab for
> >> > the
> >> > Sensitive Sheet, a popup box would appear asking for a password. Is
> >> > this even
> >> > possible?
> >> >
> >> > Help! Any answers would be much appreciated.
> >> >
> >> > Cheers!
> >> >
> >> >
>
>
>

Don't worry I figured it out.

Thanks anyways,

Dave

"coddave" wrote:

> Hi Norman,
>
> Thanks for the help! I also required some information to be restricted. Out
> of curiosity after unhiding the cell, how do you go in at a later time to
> change the password? When I click on the sheet it continues to bring the
> password screen up over and over. Is there a way to select on the tab and
> click view code again?
>
> Sincerely,
>
> Dave
>
> "Norman Jones" wrote:
>
> > Hi Michael,
> >
> > one line of Hayeso's code has wrapped making it appear as two lines. To
> > obviate the problem, replace:
> >
> > > If InputBox("Please Enter the Password for the " & Me.Name & " Sheet",
> > > "Enter Password") <> Password Then
> >
> > with:
> >
> > If InputBox("Please Enter the Password for the " _
> > & Me.Name & " Sheet", "Enter Password") _
> > <> Password Then
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
> > news:1F99BB20-811D-4A1E-8318-7DE53CB6AE03@microsoft.com...
> > > Hi--
> > >
> > > Actually, I just had a few minutes to try to implement this.
> > > Unfortunately,
> > > I'm gettiing a "Syntax Error" message, indicating that there's an
> > > end-of-statement issue. Might you have any ideas what the issue is?
> > >
> > > Thanks!
> > >
> > > "Hayeso" wrote:
> > >
> > >> Right Click the Sheet tab and select "View Code" then add
> > >> the following code. You can change the password to whatever you like.
> > >>
> > >>
> > >> Private Const Password As String = "DoubleOhSeven"
> > >>
> > >> Private Sub Worksheet_Activate()
> > >> Me.Visible = False
> > >> If InputBox("Please Enter the Password for the " & Me.Name & "
> > >> Sheet",
> > >> "Enter Password") <> Password Then
> > >> Me.Visible = False
> > >> Else
> > >> Me.Visible = True
> > >> End If
> > >> End Sub
> > >>
> > >>
> > >> "Michael Link" wrote:
> > >>
> > >> > Is it possible to protect a sheet in a workbook so that it isn't even
> > >> > viewable unless you have the password? One of the sheets in my workbook
> > >> > contaiins highly sensitive information that I don't want most folks to
> > >> > see,
> > >> > but I want users to be continue to hide and unhide other elements in
> > >> > the
> > >> > workbook to fit their needs.
> > >> >
> > >> > Is there something I can write in VBA that will do the trick? In the
> > >> > perfect
> > >> > world, what would happen is that, when a user clicked on the tab for
> > >> > the
> > >> > Sensitive Sheet, a popup box would appear asking for a password. Is
> > >> > this even
> > >> > possible?
> > >> >
> > >> > Help! Any answers would be much appreciated.
> > >> >
> > >> > Cheers!
> > >> >
> > >> >
> >
> >
> >

Worksheet and workbook protection (via tools|Protection) is very weak and isn't
meant for security.

I wouldn't share any highly sensitive data in excel.

(If the user disables macros/events, then no macro solution will help.)

And if a user can find this newsgroup or google, they can find ways to unprotect
your workbook/worksheet.

Be very careful with what you share in excel.

Michael Link wrote:
>
> Is it possible to protect a sheet in a workbook so that it isn't even
> viewable unless you have the password? One of the sheets in my workbook
> contaiins highly sensitive information that I don't want most folks to see,
> but I want users to be continue to hide and unhide other elements in the
> workbook to fit their needs.
>
> Is there something I can write in VBA that will do the trick? In the perfect
> world, what would happen is that, when a user clicked on the tab for the
> Sensitive Sheet, a popup box would appear asking for a password. Is this even
> possible?
>
> Help! Any answers would be much appreciated.
>
> Cheers!

--

Dave Peterson