Hide colum using VBA password

C

Chip Pearson

At is simplest, you could use something like the following to hide/unhide
column "E".

Sub HideUnhideWithPassword()
Dim PW As String
PW = InputBox("Enter a password:")
If StrComp(PW, "CorrectPassword", vbBinaryCompare) = 0 Then
With ThisWorkbook.Worksheets("Sheet1").Columns("E")
.Hidden = Not .Hidden
End With
Else
MsgBox "Invalid Password"
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
S

shapiro

Hello Chip,
Thank you for response, I tried but I think i am doing something wrong.
First I hid column E then I went to VBA and inserted the code. When I ran the
module it asked me to "enter a password" I typed "correctpassword" but when I
tried to unhide the column, the colums that was hidden appeared. Should it
have asked me for a password?
 
C

Chip Pearson

The code I wrote prompts for a password and if the correct password is
entered, it hides column E if it is visible, or unhides column E if it is
hidden. If you don't want to unhide the column, change

..Hidden = Not .Hidden
' to
..Hidden = True

The code doesn't prevent the user from manually hiding or unhiding the
column. To do that, you'll need to protect the sheet with a password and use
that password in the code:

Sub HideUnhideWithPassword()
Dim PW As String
PW = InputBox("Enter a password:")
If StrComp(PW, "CorrectPassword", vbBinaryCompare) = 0 Then
ActiveSheet.Unprotect Password:=PW
With ThisWorkbook.Worksheets("Sheet1").Columns("E")
.Hidden = True
End With
ActiveSheet.Protect Password:=PW
Else
MsgBox "Invalid Password"
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
S

shapiro

Thanks Chip. It worked perfectly!!

Chip Pearson said:
The code I wrote prompts for a password and if the correct password is
entered, it hides column E if it is visible, or unhides column E if it is
hidden. If you don't want to unhide the column, change

..Hidden = Not .Hidden
' to
..Hidden = True

The code doesn't prevent the user from manually hiding or unhiding the
column. To do that, you'll need to protect the sheet with a password and use
that password in the code:

Sub HideUnhideWithPassword()
Dim PW As String
PW = InputBox("Enter a password:")
If StrComp(PW, "CorrectPassword", vbBinaryCompare) = 0 Then
ActiveSheet.Unprotect Password:=PW
With ThisWorkbook.Worksheets("Sheet1").Columns("E")
.Hidden = True
End With
ActiveSheet.Protect Password:=PW
Else
MsgBox "Invalid Password"
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
Click to expand...
 
Top