Password lock for a control button.

XrayLemi

Board Regular
Joined
Aug 1, 2018
Messages
88
Office Version
  1. 365
Platform
  1. Windows
Hello again all. I hope you are safe and well.

If this has already been solved here, I did not find it. So I apologize.

I am trying to add a password to a control button placed on a sheet. I do not want the macro attached to the control button to execute unless the password is correct. The following code I used was copied, manipulated, and is on the sheet code area. When the control button is selected, the input box comes up as it should. You can put in the password. When the password is correct, the macro runs. HOWEVER, if you put in the wrong password, the MsgBox opens up and tells you that you put in the incorrect password. Yet the macro still runs when it shouldn't. What did I forget or do wrong?
VBA Code:
Private Sub CommandButton2_Click()
Dim password As Variant
password = Application.InputBox("Enter Password", "Password Protected")

Select Case password
    Case Is = False
        'do nothing
    Case Is = "ABCDEFG"
    Case Else
        MsgBox "Incorrect Password"
End Select
    WSMaintainance
End Sub
Thank you in advance!
Jim
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,176
Office Version
  1. 365
Platform
  1. Windows
Most call a userform with a textbox with PasswordChar property to mask the password as entered.
VBA Code:
Private Sub CommandButton2_Click()
Dim password As Variant
password = Application.InputBox("Enter Password", "Password Protected")

Select Case password
    Case Is = False
        'do nothing
    Case Is = "ABCDEFG"
            WSMaintainance
    Case Else
        MsgBox "Incorrect Password"
End Select
End Sub
 

XrayLemi

Board Regular
Joined
Aug 1, 2018
Messages
88
Office Version
  1. 365
Platform
  1. Windows
Hi Kenneth,
I have never created a userform. This will take me some time to figure out. I am also not sure if I need to do something more complicated than what I started already. I am the only one using the button, and when I do, I am alone. I just don't want anyone else to use it.
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,176
Office Version
  1. 365
Platform
  1. Windows
Password would be needed if someone might log on from your computer. If just you, change ken to yours and:
VBA Code:
Private Sub CommandButton2_Click()
  If Environ("username")="ken" Then WSMaintainance
End Sub
In Immediate window view of VBE, ?Environ("username"), and Enter key will show your username. This is what Debug.Print Environ("username") would show. Or, use a MsgBox to find yours if you don't know it.
 

XrayLemi

Board Regular
Joined
Aug 1, 2018
Messages
88
Office Version
  1. 365
Platform
  1. Windows
Ken,
That works. you did bring up a good point though. The files are used regularly during the week. Any logged in station will be used in a pinch, including mine. So even if I don't do changes, The files may / will be open and used by others with my log in.
 

XrayLemi

Board Regular
Joined
Aug 1, 2018
Messages
88
Office Version
  1. 365
Platform
  1. Windows
Hi Ken,
I REALLY like the Environ code you gave me. That will work perfectly for another project I have going on!
With that said.....
I did what you originally suggested. I made my first user form. This did a few things for me. It allowed me to keep the password private. In a pinch others can use my log in. and I can keep the user form open while I do the maintenance. Once I enter the password, the Activate button appears, and will stay open until I close the form. I even figured out how to set the showmodal attribute so I don't have to close the user form to work on the sheet!

here is the code I used for the user form
VBA Code:
Private Sub CommandButton1_Click()
If TextBox1 = "123456" Then
CommandButton2.Visible = True
Else
MsgBox "Wrong Password"
End If
End Sub
Private Sub CommandButton2_Click()   'Activate Button
WSMaintainance
End Sub

Thank you for the great ideas, and for making me think!
Jim
 

Attachments

  • Userform2.png
    Userform2.png
    18.2 KB · Views: 2
  • Userform3.png
    Userform3.png
    18.9 KB · Views: 2
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,931
Messages
5,639,061
Members
417,067
Latest member
rohitbabshet

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