Protect and Hide a Worksheet tab?

JackDanIce

Well-known Member

Joined

Feb 3, 2010

Messages

9,922

Office Version

1. 365

Platform

1. Windows

• May 3, 2011

• #2

Read through:
http://www.ozgrid.com/Excel/hiding-excel-worksheets.htm

There's a description of very hidden which should do as you require

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• May 3, 2011

• #3

Try like this

Code:

Sub HideSheet()
With Sheets("Sheet1")
    .Protect Password:="pw"
    .Visible = xlSheetVeryHidden
End With
End Sub

 

Upvote 0

FinancialAnalystKid

Well-known Member

Joined

Oct 14, 2004

Messages

779

• May 3, 2011

• #4

VoG said:

Try like this

Code:

Sub HideSheet()
With Sheets("Sheet1")
    .Protect Password:="pw"
    .Visible = xlSheetVeryHidden
End With
End Sub

Click to expand...

Hmm...

Which leads me to: Is there a way to set a password so if someone wants to unhide a tab they are asked for a password. They need permission to view it?

I worry that if someone knows VBA and knows there is a hidden sheet they will be able to use the above script to unhide worksheets!

 

Last edited: May 3, 2011

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• May 3, 2011

• #5

Not telling

Code:

Sub UnHideSheet()
With Sheets("Sheet1")
    .Unprotect Password:="pw"
    .Visible = xlSheetVisible
End With
End Sub

 

Upvote 0

JackDanIce

Well-known Member

Joined

Feb 3, 2010

Messages

9,922

Office Version

1. 365

Platform

1. Windows

• May 3, 2011

• #6

Oh VoG!!

 

Upvote 0

C

ChrisM

Well-known Member

Joined

Jun 11, 2002

Messages

2,129

• May 3, 2011

• #7

Make sure you set the PW for the VB editor to "lock for viewing" as well, otherwise any user can open up VB, select your sheet, and change the visible setting to 'Visible'.

You realize that no password in Excel is truly secure? I would never put any truly confidential data in a public workbook.

 

Upvote 0

FinancialAnalystKid

Well-known Member

Joined

Oct 14, 2004

Messages

779

• May 3, 2011

• #8

VoG said:

Not telling

Click to expand...

LOL I figured it out but...

Which leads me to: Is there a way to set a password so if someone wants to unhide a tab they are asked for a password. They need permission to view it?

I worry that if someone knows VBA and knows there is a hidden sheet they will be able to use the above script to unhide worksheets!

 

Upvote 0

FinancialAnalystKid

Well-known Member

Joined

Oct 14, 2004

Messages

779

• May 3, 2011

• #9

ChrisM said:

Make sure you set the PW for the VB editor to "lock for viewing" as well, otherwise any user can open up VB, select your sheet, and change the visible setting to 'Visible'.

You realize that no password in Excel is truly secure? I would never put any truly confidential data in a public workbook.

Click to expand...

The script will be on my Personal computer right? I'm not saving it in the sheet it will only be with 'me' so-to-speak if I only save it in my personal.xlsb right?

Now, how do you lock for viewing anyway?

 

Upvote 0

C

ChrisM

Well-known Member

Joined

Jun 11, 2002

Messages

2,129

• May 3, 2011

• #10

Open VB Editor, go to menu Tools/VBA Project Properties

Then select tab "Protection"

Check box and type a password. Save file, then close and reopen your spreadsheet, open the VB Editor again and note the difference.

Regardless of whether the macro code is in the workbook or your personal workbook, it all depends on where the physical data resides. If it is in your regular workbook then be careful about sending it out. Do some google searches on excel security and you will quickly see that you don't want to put your life in its hands.

 

Upvote 0