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!!!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

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,456
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,456

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,031
Messages
5,856,936
Members
431,841
Latest member
jaybeem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top