Unhide a Column with a password

E

-emma-

Board Regular

Joined

Jul 14, 2006

Messages

180

Office Version

1. 365

Platform

1. Windows

• Feb 20, 2010

• #11

I currently have this :

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub CommandButton1_Click()
Dim ans As String, bVisible As Boolean
bVisible = Range("B:B").EntireColumn.Hidden
If Not bVisible Then Exit Sub
ans = InputBox("Enter your password", "Password required")
If ans = "xxxxxxxxxx" Then
Range("B:B").EntireColumn.Hidden = False
Else
MsgBox "Sorry, wrong password."
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then Range("B:B").EntireColumn.Hidden = True
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

What do you suggest I change it to???

 

Upvote 0

M

mvptomlinson

Well-known Member

Joined

Mar 10, 2008

Messages

2,638

• Feb 20, 2010

• #12

I can see Lenze's point about protection, but workbook structure protection has nothing to do with whether you can or cannot hide/unhide a column. WorkSHEET protection will prevent you from doing so.

If you are worried about users manually un-hiding a column, you can unlock all cells you want them to edit, hide column B then protect the worksheet and remember to select 'Format Columns' in the protection window (along with 'Select Locked Cells' and 'Select Unlocked Cells').

As for adding a second button to re-hide column B, let's say it's called CommandButton2, then use:

Code:

Private Sub CommandButton2_Click()
Range("B:B").EntireColumn.Hidden = True
End Sub

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• Feb 20, 2010

• #13

mvptomlinsen is correct. You only need to protect the sheets instead of the workbook. I must have been having a senior moment, not to mention posting wihtout testing. Anyway something like this in his code

Rich (BB code):

Private Sub CommandButton1_Click()
Dim ans As String, bVisible As Boolean
bVisible = Range("B:B").EntireColumn.Hidden
If Not bVisible Then Exit Sub
ans = InputBox("Enter your password", "Password required")
If ans = "xxxxxxxxxx" Then
Activesheet.Unprotect "password"
Range("B:B").EntireColumn.Hidden = False
Else
MsgBox "Sorry, wrong password."
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then 
Range("B:B").EntireColumn.Hidden = True
ActiveSheet.Protect "password"
End If
End Sub

lenze

 

Upvote 0

E

-emma-

Board Regular

Joined

Jul 14, 2006

Messages

180

Office Version

1. 365

Platform

1. Windows

• Feb 20, 2010

• #14

Great - ive sorted it now Thank you.

1 more thing...and its not your blood

I can give them a button to make sure the info is hidden, but this doesnt stop them unhiding column B, looking at the information, then saving the info as it is.

I will either need to make sure column B is hidden when they save the document, or its hidden when next open.

Any ideas guys?

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• Feb 20, 2010

• #15

As long as you protect the sheet with the above code from mvptomlinsen and the lines I added, this should not be a problem. If the Sheet is protected, the user can not unhide Columns

lenze

 

Upvote 0

baitmaster

Well-known Member

Joined

Mar 12, 2009

Messages

2,042

• Feb 20, 2010

• #16

Depending how sensitive the data is, you might have to consider a different approach altogether. Hiding a column, even with passwords, won't stop anyone from finding the data in it if they want to

Simply writing formulae on a new sheet to look at each corresponding cell, will reveal the contents. I'm not aware of any way to prevent this

 

Upvote 0

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Feb 20, 2010

• #17

Using sheet protection to hide sensitive data is very problimatic.

Put some data in B1:B20
Hide column B.
Protect the sheet.
Select A1:C20
Copy
Navigate to a different sheet
Paste
inspect column B's data at leisure

How sensitive is the data in column B?
How bad is it if an unauthorized person sees column B?

BTW, If your users are like mine, some know more Excel than I think they do, some know less. All of them know a clever someone (e.g. spouse, friend) who knows Excel.

 

Last edited: Feb 20, 2010

Upvote 0

bjurney

Active Member

Joined

Aug 24, 2009

Messages

320

• Feb 20, 2010

• #18

You could put a formula like this in your cells in Column B

=IF(A1="My words","My sensitive information",(RAND()*(10000000000000000000+99999999999999900000)))


That way in cell A1, or where ever you choose, you would have to enter a "password" so to speak. If someone tries to copy and paste sheet into a new sheet and unhides comlumn B they will only see a random 20 digit number provided you have the workbook protected

 

Upvote 0

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Feb 20, 2010

• #19

Edit my previous post to PasteSpecial/Values or consider baitmaster's post.

Excel's sheet protection is designed to protect formulas etc. against users with clumsy fingers.
It is not designed for keeping secrets.

A veryHidden worksheet holding the secret data might offer a little more privacy, but not much.

Excel is not secure.

 

Upvote 0

M

Mhep13

New Member

Joined

Jun 22, 2005

Messages

13

• Oct 6, 2010

• #20

lenze said:

You will need 2 codes! The one for the Button in a regular module

Code:

Sub UnHideB()
rspn = InputBox("Enter Password for Column B")
If rspn = "password" Then
ThisWorkbook.Unprotect "bookpassword"
Sheet1.Columns(2).Hidden = False
End If
End Sub

The one for Column "B" change in the Sheet module

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
rspn = MsgBox(" Are you finished?", vbYesNo)
If rspn = vbYes Then Columns(2).Hidden = True
ThisWorkbook.Protect "bookpassword"
End Sub

Note that the WorkBook must be protected for "Structure" to prevent manually unhidding Column "B"!
HTH
lenze

Click to expand...

Hi, I have been reading your posts on this subject and I have managed to get the button to reveal the columns (instead of column 'B' I have column 'y,z,aa' ie (25,26,27).

But I am struggling with the code for the adjustment part. I need the code to monitor changes in columns y, z, aa and then ask the question 'are you finished'. Could you help me with what that code would be?

Also upon closing I would want to ensure that columns 'y,z,aa' are always hidden. Hence would need to click the button to reveal as above. Could you help me with the code for this?

I am using excel for mac 2004.

Thanks in anticipation.

MH

 

Upvote 0