Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel hide cells or single sheet

Posted on 2009-12-20
15
Medium Priority
?
614 Views
Last Modified: 2012-05-08
Hi,
Excel 2007
 
I have a sheet with financial tables that I would like to password protect... but the tricky part is I only want certain cells on the same sheet to be hidden "invisible" until a password is entered. Any other person can see the rest of the sheet and use the visible cells.

If this is impossible an alternative is that i could password protect "sheet2" so how can i make it so the work book can be opened, sheet 1 is available but sheet 2 cannot be opened unless a password is entered?

Thanks,

0
Comment
Question by:flyingsquirel
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26092645
How secure do you need this to be? If you need any real level of security, then you won't get it with a single Excel workbook - the only half way decent passwords are the ones to open workbooks. All the others are feeble.
0
 
LVL 4

Author Comment

by:flyingsquirel
ID: 26092676
Its for an internal document so it doesn't need to be that secure. Its mainly to streamline costing and hide a few rates.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26092730
You could hide a worksheet and then protect the workbook for structure using a password. That way the sheet can't be made visible without the password.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 4

Author Comment

by:flyingsquirel
ID: 26092861
I tried that but it is very cumbersome. Every time an admin wants to unhide they have to go through the whole process of unprotecting, unhiding then working. Then once they are done they need to reverse the whole process to protect it again.

Im really hoping for a button or something that will unhide the cells or the sheet.

I'm happy for any suggestions even using Adobe suite (i have this) or creating an exe or something. It does need to be fairly user friendly though.

Thanks,
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26092876
You could use a simple macro that prompts for the password and then unhides the sheet if it's correct?
0
 
LVL 4

Author Comment

by:flyingsquirel
ID: 26093022
YES! that is what im looking for. Ive tried Google and cant find the macro that would do this.
0
 
LVL 23

Accepted Solution

by:
ahammar earned 1000 total points
ID: 26093365
Here is a sample file of probably how you would want to do it.  You would have to build a little basic form, or import this one into your Excel file.  I don't know how experienced you are with VBA.
You could also do it without a form, but when you type the password, you would see the letters instead of the * symbols...

Download the file and run the macro to show Sheet2 (Ctrl + d)
The password is "password"

The password to view the code is: "xxx"

:-)
Albert

ShowSheet.xls
0
 
LVL 4

Author Comment

by:flyingsquirel
ID: 26093425
great i was able to get the sheet to open after enabling macros. But i dont know where to enter the "xxx" password.

Also is there a way to put this on a  button or link on the first sheet?
0
 
LVL 4

Author Comment

by:flyingsquirel
ID: 26093471
Okay found the VB editor. Just need to know how to make this button enabled.

Thanks again.
0
 
LVL 5

Assisted Solution

by:sir plus
sir plus earned 1000 total points
ID: 26094277
Hi
ahammar: ups to you -I likeee :-)
flyingsquirel: You dont need to make a button !!!!!

Nooooooo :-/
The button will just entice peeps to try crack it

ahammar did it all for you already and it is soooo sneaky
Just push ctrl key AND "d" at the same time ie "CTRL+d"

the password to show the sheet is "password"
just type "password" in the box that pops up and it shows the sheet

the xxx one is to unlock the code which you wont need to do to use this

I am going to suggest a few changes

1/That Selecting sheet 1 (Clicking its tab) will hide sheet 2 again again

2/That saving the book hides sheet 2 automatically before saving

3/That you dont save the book automatically if its closed with sheet 2 visible as this may mean that changes which are not meant to be saved will be  (& can only end in tears). (also I think the test was the opposite of what was intended -but what I propose doesnt need it anyway)

4/Make the standard module Private so there is no macro showing in the list announcing that there is s hidden sheet.

5/Make it refer to sheet 2 in "Thisworkbook" to keep it safer.

6/Instead of running from CTRL+d which means that you have the macro exposed to arouse curiosity, that you hide that by making the module private.
Unfortunately we now need another way to trigger the macro

Look at the sheet 1 code
If we pick 2 cells that arent being used and say type 21 in A1 and h in B2 then the password macro runs and those cells are emptied again as well.

Doing it like this means  there is no outward and visible signs to arrouse curiosity and all they see is a locked project.
 
I have taken liberty of making these changes

I have added these to this book attached
Option Explicit
Const COI1 = "A1"
Const COI1Val = 21
Const COI2 = "B2"
Const COI2Val = "h"

Private Sub Worksheet_Activate()
'Activating sheet1 hides sheet2 unless you have typed the following on sheet 2 (the hidden sheet)
' COI1Val in COI1 An COI2Val in COI2 
    If ThisWorkbook.Sheets(cSheetNameToHide).Range(COI1).Value <> COI1Val _
    And ThisWorkbook.Sheets(cSheetNameToHide).Range(COI2).Value <> COI2Val Then _
    ThisWorkbook.Sheets(cSheetNameToHide).Visible = xlSheetVeryHidden
End Sub

'On Sheet 1 enter COI1Val in COI1 then COI2Val in COI2 to trigger the unhide code
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(RowAbsolute:=False, ColumnAbsolute:=False) = COI2 _
    And Me.Range(COI1).Value = COI1Val _
    And Target.Range(COI1).Value = COI2Val Then
        ShowRateSheet
        Me.Range(COI1).Value = ""
        Me.Range(COI2).Value = ""
    End If
End Sub

Open in new window

0
 
LVL 5

Expert Comment

by:sir plus
ID: 26094284
It Posted before I clicked to add the file

Here tis!
Hide-and-unhide-a-sheet-xxx-.xls
0
 
LVL 5

Expert Comment

by:sir plus
ID: 26094300

Const COI1 = "A1"
Const COI1Val = 21
Const COI2 = "B2"
Const COI2Val = "h"
typing 21 in A1 & h in B2 on sheets stops sheet 2 from hiding when you click the sheet 1 tab/activate it!
0
 
LVL 4

Author Comment

by:flyingsquirel
ID: 26099953
Dude that is EPIC! Both of you have solved my question perfectly. Thank you very much.
0
 
LVL 23

Expert Comment

by:ahammar
ID: 26101759
Thanks for the points and the grade!  I'm glad we got it working for you.

sirplus,
Excellent additions.  Very good ideas!

I'm glad flyingsquirel liked our work...

:-)
Albert
0
 
LVL 5

Expert Comment

by:sir plus
ID: 26101965
Thanks for the points fs
ahammar really answered it perfectly well

PS
One more upgrade is
Make it clear the cells in sheet 2 when you activate sheet 1


Private Sub Worksheet_Activate() 
'Activating sheet1 hides sheet2 unless you have typed the following on sheet 2 (the hidden sheet) 
' COI1Val in COI1 An COI2Val in COI2  
    If ThisWorkbook.Sheets(cSheetNameToHide).Range(COI1).Value <> COI1Val _ 
    And ThisWorkbook.Sheets(cSheetNameToHide).Range(COI2).Value <> COI2Val Then _ 
    ThisWorkbook.Sheets(cSheetNameToHide).Visible = xlSheetVeryHidden
ELSE 
    ThisWorkbook.Sheets(cSheetNameToHide).Range(COI1).Value = "" 
    ThisWorkbook.Sheets(cSheetNameToHide).Range(COI2).Value = "" 
End Sub

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question