Unhide a Column with a password

[-emma-]

Hi all,

I have column B hidden as it holds information that only certain people should be able to see.

I have a button in column A which when pressed, I would like the following...

1- a prompt box to ask for a password
2-once the password is entered, Column B unhides.
3-once an entry in Column B has been made, it automatically hides again.

I hope im not asking too much, but any help would be great thanks.

Emma x

 

[VoG]

Try this, all the code goes in the sheet's code module. Change pw to suit.

Private Sub CommandButton1_Click()
If InputBox("Enter password") <> "pw" Then Exit Sub
Me.Columns("B").Hidden = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then Target.EntireColumn.Hidden = True
End Sub

 

[lenze]

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

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

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

 

[-emma-]

Im having a problem getting it to work when i press the button...

Can you advise?

 

[mvptomlinson]

These two events might help. First is assigned to CommandButton1, second is the Worksheet's Change event triggered if a change is made to a cell in column 2 (B).

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 = "your_password" 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

Hope that helps!

 

[VoG]

Im having a problem getting it to work when i press the button...

Can you advise?

Where did you add the button from? What is the name of the button?

 

[-emma-]

Hi,

Thanks all for the replies...

I went with mvptomlinson's answer cos it worked.

Thanks again

Emma x

 

[lenze]

But, see my post!! It will do you no good to hide the column if you DO NOT protect the WorkBook for structure!!

lenze

 

[-emma-]

lenze - the people using the workbook are not good on excel so will not know how to. Also, the row and column headings will be hidden so they will not be able to unhide it easily...

Thanks anyway...

 

[lenze]

lenze - the people using the workbook are not good on excel so will not know how to. Also, the row and column headings will be hidden so they will not be able to unhide it easily...

Thanks anyway...

Surely you don't seriously believe that!!!
If the data is sensitive, I would suspect you would want to use all means posible to protect it