hide sheet tab

G

gav meredith

Hi,

i have a workbook with which i want one of the sheets to not be viewed
without a password. Is this possible?? I dont simply want to hide the sheet
as a person could unhide it. The contents of the sheet are not for all eyes.

Any suggestions????? Cheers!!!!
 
K

kkknie

In code you can use:

Sheets("Sheet1").Visible = xlVeryHidden

Although, anyone who knows how to write code can unhide it if you don'
put a password on it somewhere. I do this a lot since most of my user
are not very "sophisticated".
 
V

Vasant Nanavati

You can hide the sheet and then protect the workbook (not the worksheet)
with a passsword.
 
G

gav meredith

yes but the person accessing the workbook could still unhide the sheet. All
should have access to the workbook but only a few should have access to the
hidden sheet. It is the contents of this sheet that i want hidden from a
select few......
 
V

Vasant Nanavati

I didn't say *save* the workbook with a password; I said *protect* it.

Tools | Protection | Protect Workbook

after you hide the worksheet.
 
C

chris

in your projects window go to ThisWorkbook and in your code window''s left dropdown box choose WorkBook, Right dropdown box choose SheetActivate. Then put in this code

Private Sub Workbook_SheetActivate(ByVal Sh As Object) "this will be generated for you


If Sh.Name = "My Hide Sheet Name" Then Worksheets("My Default Sheet Name").Activat

End Sub "this will be generated for you


----- gav meredith wrote: ----

Hi

i have a workbook with which i want one of the sheets to not be viewe
without a password. Is this possible?? I dont simply want to hide the shee
as a person could unhide it. The contents of the sheet are not for all eyes

Any suggestions????? Cheers!!!
 
C

chris

you can put a inputbox function in also to act as a passwor

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "My Hide Sheet Name" Then
x = inputbox("Input Password")
if x = YourPassword Then Exit Su
Worksheets("My Default Sheet Name").Activat
end if
End Sub

----- chris wrote: ----

in your projects window go to ThisWorkbook and in your code window''s left dropdown box choose WorkBook, Right dropdown box choose SheetActivate. Then put in this code


Private Sub Workbook_SheetActivate(ByVal Sh As Object) "this will be generated for you


If Sh.Name = "My Hide Sheet Name" Then Worksheets("My Default Sheet Name").Activat


End Sub "this will be generated for you



----- gav meredith wrote: ----

Hi

i have a workbook with which i want one of the sheets to not be viewe
without a password. Is this possible?? I dont simply want to hide the shee
as a person could unhide it. The contents of the sheet are not for all eyes

Any suggestions????? Cheers!!!
 
D

Don Guillett

If sheet is hidden with very hidden then it can only be unhidden with code.
And, if the code is protected they would have a hard time, especially if
they didn't know the ws name.
 
G

gav meredith

um, neither did i???? When a person unprotects the workbook they could then
unhide the sheet anyway. The idea is to give certain users access to this
particular worksheet within the workbook....
 
V

Vasant Nanavati

I guess one of us is not understanding the other clearly.

Why would you give the non-authorized users the password to unprotect the
workbook?

Workbook protection protects only the structure of the workbook. This means
that without the password, users cannot insert, delete, hide, unhide or
rename worksheets.
They can do whatever else they would do with an unprotected workbook.
 
G

gav meredith

they both sound good.......could you explain a little more on how to
implement them???
chris said:
you can put a inputbox function in also to act as a password

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "My Hide Sheet Name" Then
x = inputbox("Input Password")
if x = YourPassword Then Exit Sub
Worksheets("My Default Sheet Name").Activate
end if
End Sub

----- chris wrote: -----

in your projects window go to ThisWorkbook and in your code window''s
Click to expand...
left dropdown box choose WorkBook, Right dropdown box choose SheetActivate.
Then put in this code:
 
G

gav meredith

that is correct. The users are to be able to alter certain parts of the
workbook but NOT one particular sheet. This sheet basically is to be
accessed by users with a particular security level. They do need to amend
certain parts of the workbook. All i am after is for a sheet called VKnew to
be hidden.
 
C

chris

1 more thing: your gonna have to do something like this to prevent the enduser from seeing the data on the sheet while the inputbox is up

If UCase(Sh.Name) = "My Hide Sheet Name" The
Y = Sh.Columns(1).ColumnWidt
Sh.Columns(1).ColumnWidth = 500 (a huge number so they Can't see whats on the whole sheet
x = InputBox("pass word"
If x = "DOG" The
Sh.Columns(1).ColumnWidth =
Exit Su
End I
Sh.Columns(1).ColumnWidth =
Worksheets("My Default Sheet Name").Activat
End I

----- chris wrote: ----

you can put a inputbox function in also to act as a passwor

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "My Hide Sheet Name" Then
x = inputbox("Input Password")
if x = YourPassword Then Exit Su
Worksheets("My Default Sheet Name").Activat
end if
End Sub

----- chris wrote: ----

in your projects window go to ThisWorkbook and in your code window''s left dropdown box choose WorkBook, Right dropdown box choose SheetActivate. Then put in this code


Private Sub Workbook_SheetActivate(ByVal Sh As Object) "this will be generated for you


If Sh.Name = "My Hide Sheet Name" Then Worksheets("My Default Sheet Name").Activat


End Sub "this will be generated for you



----- gav meredith wrote: ----

Hi

i have a workbook with which i want one of the sheets to not be viewe
without a password. Is this possible?? I dont simply want to hide the shee
as a person could unhide it. The contents of the sheet are not for all eyes

Any suggestions????? Cheers!!!
 
V

Vasant Nanavati

Chris, in both your solutions the users are still able to see the worksheet,
in the first case by holding the mouse button down on the sheet tab, and in
the second case while the input box is on the screen. They may not be able
to activate it and do anything with it but they can certainly view the
contents (unless you have other code that moves the cellpointer to an unused
part of the sheet when it is activated, which would be inconvenient for the
authorized users).

--

Vasant

chris said:
you can put a inputbox function in also to act as a password

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "My Hide Sheet Name" Then
x = inputbox("Input Password")
if x = YourPassword Then Exit Sub
Worksheets("My Default Sheet Name").Activate
end if
End Sub

----- chris wrote: -----

in your projects window go to ThisWorkbook and in your code window''s
Click to expand...
left dropdown box choose WorkBook, Right dropdown box choose SheetActivate.
Then put in this code:
 
G

gav meredith

not a problem...thank you!!! Could you please elaborate a bit more on the
input box and how to implement this code?????
chris said:
1 more thing: your gonna have to do something like this to prevent the
Click to expand...
enduser from seeing the data on the sheet while the inputbox is up.
If UCase(Sh.Name) = "My Hide Sheet Name" Then
Y = Sh.Columns(1).ColumnWidth
Sh.Columns(1).ColumnWidth = 500 (a huge number so they Can't see whats on the whole sheet)
x = InputBox("pass word")
If x = "DOG" Then
Sh.Columns(1).ColumnWidth = Y
Exit Sub
End If
Sh.Columns(1).ColumnWidth = Y
Worksheets("My Default Sheet Name").Activate
End If

----- chris wrote: -----

you can put a inputbox function in also to act as a password

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "My Hide Sheet Name" Then
x = inputbox("Input Password")
if x = YourPassword Then Exit Sub
Worksheets("My Default Sheet Name").Activate
end if
End Sub

----- chris wrote: -----

in your projects window go to ThisWorkbook and in your code
Click to expand...
window''s left dropdown box choose WorkBook, Right dropdown box choose
SheetActivate. Then put in this code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Click to expand...
"this will be generated for you"
 
V

Vasant Nanavati

My solution will do exactly what you want to accomplish. Why don't you try
it first?

Don's solution is also practical and easy to implement but does not give
authorized users an easy way to view the sheet with a password.

Chris's amended solution will also work but is too complicated, in my view,
for the simple task you want to accomplish.

Try the proposed solutions before you second-guess! This is not a difficult
problem.

--

Vasant
 
G

gav meredith

aha, now that is what i dont want. I dont want the user to be able to see
the VKnew sheet but do want them to access the others.......
 
G

gav meredith

vasant, that seems to do the trick...thank you!!!!!
and thanks to everybody else also!!!!
 
G

Gord Dibben

gav

My 2 cents worth.

There are three types of protection and passwording(4 if you count VBA
projects)

1. Sheet Protection is what you need to "lock" or "unlock" cells for
editing. Can be passworded.

Protecting elements from all users. You can prevent users from inserting,
deleting, and formatting rows and columns, from changing the contents of
locked cells, and from moving the cursor to cells that are locked or to cells
that are unlocked.

Each sheet must be done individually unless you use a Macro.

2. Workbook Protection is used to lock the structure. This is what Vasant
is referring to. Can be passworded.

You can prevent users from adding or deleting worksheets, or displaying hidden
worksheets. You can also prevent users from changing the sizes or positions of
the windows you set up to display a workbook. These protections apply to the
entire workbook. This does not protect the sheet from editing.

3. Then there is File Protection. You put a password on the file so it
cannot be opened with out the PW.

To prevent anyone from viewing an Excel file you need to password-protect the
file from being opened in the first place.

XL97.....File>Save As>Options>Password to open

XL2002...File>Save As>Tools>General Options>Password to open

Gord Dibben Excel MVP
 
Top