Password Button

willisbr

New Member
Joined
Mar 2, 2006
Messages
29
How can I create a button function that will only work after password entry?

Example: I need a toggle button to hide/unhide columns but I want to password protect the toggle.

Is this possible? Thanks!!!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Doug.T

Board Regular
Joined
Sep 19, 2006
Messages
80
VBA solution:

Private Sub ToggleButton1_Click()
a = InputBox("Enter password:", "Enter Password")
If a <> "mypassword" Then
MsgBox "wrong password"
End
End If
'do hide/unhide stuff if password matches
End Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello willisbr,
If you use an ActiveX button (a CommandButton from the Controls Toolbox toolbar)
then something like this should work for what you've asked.
It assumes you want the password restriction before hiding or showing the columns.
Code:
Private Sub CommandButton1_Click()
Dim Pw$
Pw = InputBox("Enter password to proceed", "Enter Password")
If Len(Pw) = 0 Then Exit Sub
If Pw <> "YourPassword" Then MsgBox "Incorrect password.", , "Invalid Pasword": Exit Sub
With CommandButton1
  If .Caption = "Hide Columns" Then
    Columns("A:D").Hidden = True
    .Caption = "Show Columns"
  Else
    Columns("A:D").Hidden = False
    .Caption = "Hide Columns"
  End If
End With
End Sub
You'll want to make sure to change the password required from "YourPassword" to
whatever you want to use, and change the caption of the commandbutton to either
"Hide Columns" or "Show Columns" to begin with.
 

willisbr

New Member
Joined
Mar 2, 2006
Messages
29
I'm barely getting it to work. Can you look at my code please to how I'm using the toggle? Thanks!

Private Sub ToggleButton1_Click()
If ToggleButton1.Value = False Then
Range("C:D,F:G,I:J,M:P,R:S,U:V,X:Y,AA:AD,AF:AG").Select
Selection.EntireColumn.Hidden = True
Range("C5").Select
ToggleButton1.Caption = "Show Raw Data"



Else

Range("C:D,F:G,I:J,M:P,R:S,U:V,X:Y,AA:AD,AF:AG").Select
Selection.EntireColumn.Hidden = False
ToggleButton1.Caption = "Hide Raw Data"
End If
End Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453

ADVERTISEMENT

I'm not sure what you mean by "barely getting it to work".
In my experience it either is or it isn't. :biggrin:
This is working for me just fine. Is there someting about it you want to change?
(Note, there's no reason to select the columns in order to work with them.)
Code:
Private Sub ToggleButton1_Click()
a = InputBox("Enter password:", "Enter Password")
If a <> "mypassword" Then
MsgBox "wrong password"
End
End If
If ToggleButton1.Value = False Then
  Range("C:D, F:G, I:J, M:P, R:S, U:V, X:Y, AA:AD, AF:AG").EntireColumn.Hidden = True
  ToggleButton1.Caption = "Show Raw Data"
Else
  Range("C:D, F:G, I:J, M:P, R:S, U:V, X:Y, AA:AD, AF:AG").EntireColumn.Hidden = False
  ToggleButton1.Caption = "Hide Raw Data"
End If
End Sub
Personally, I'd make it not require a password to hide the columns, just to unhide them
but I don't know if there's a reason you want it for both so...
 

willisbr

New Member
Joined
Mar 2, 2006
Messages
29
I'm getting a runtime error 1004 when I run this code. Maybe it's because i'm using an older version? Excel 97.

The only one I've been able to get working is using the selection of each column like
Range("C:D,F:G,I:J,M:P,R:S,U:V,X:Y,AA:AD,AF:AG").Select
Selection.EntireColumn.Hidden = False

The runtime error is selecting
Range("C:D, F:G, I:J, M:P, R:S, U:V, X:Y, AA:AD, AF:AG").EntireColumn.Hidden = False
 

willisbr

New Member
Joined
Mar 2, 2006
Messages
29
Ok I got it to work by using this:

Private Sub ToggleButton1_Click()

a = InputBox("Enter password:", "Enter Password")
If a <> "go" Then
MsgBox "wrong password"
End
End If
If ToggleButton1.Value = False Then
Range("C:D,F:G,I:J,M:P,R:S,U:V,X:Y,AA:AD,AF:AG").Select
Selection.EntireColumn.Hidden = True

ToggleButton1.Caption = "Show Raw Data"
Else
Range("C:D,F:G,I:J,M:P,R:S,U:V,X:Y,AA:AD,AF:AG").Select
Selection.EntireColumn.Hidden = False
Range("C5").Select
ToggleButton1.Caption = "Hide Raw Data"
End If
End Sub

I think the older version does not recognize
Range("C:D, F:G, I:J, M:P, R:S, U:V, X:Y, AA:AD, AF:AG").EntireColumn.Hidden = False

Lastly, I would like anyone to be able to just hit the toggle to hide the columns but to unhide I want it password protected. Is there a way to only make the password request come up if it's requesting an unhide? Thanks again for your help, brian
 

Watch MrExcel Video

Forum statistics

Threads
1,114,384
Messages
5,547,638
Members
410,805
Latest member
Ginoji
Top