Forum Discussion

Jn12345's avatar
Jn12345
Brass Contributor
Nov 27, 2023

Button Macro not to be pressed unless a password is provided

Hello all,   So i have an option button and i have a password in my macro so that the code doesnt run unless the correct password is input, however, the issue is that the button still clicks (even ...
excel
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Nov 27, 2023

Jn12345 

If you prompt for the password in the macro associated with the option button, the button has already been clicked when the prompt is displayed. You'd have to turn the button off in the code, but the user will still see it being turned on, then off again...

Jn12345's avatar
Jn12345
Brass Contributor
Nov 29, 2023
Okay im not sure how to do that since im kinda new to macros and when i record what happens when i press the button there is nothing recorded in my VBA. Also i think there is a way to kind of freeze the screen while the macro is being run since i was doing that on some other macros. Ill probably add that to this button also
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 29, 2023

    Jn12345 

    Here is an example:

     

    Sub ClockToDegrees()
    Dim Password As Variant
    Password = Application.InputBox(Prompt:="Please Enter Password (WARNING: Date Will Be Cleared)", Type:=2)
    Select Case Password
        Case False
            'Nothing
        Case "FT007"
            ActiveSheet.Unprotect Password:="Protect123!"
            ' Change the name of the option button as needed
            ActiveSheet.OptionButtons("Option Button 1").Value = False
            ' More code here
            ' ...
            ActiveSheet.Protect Password:="Protect123!", DrawingObjects:=True, Contents:=True, Scenarios:=True
        Case Else
            MsgBox "The Password You Entered Is Incorrect", vbExclamation
    End Select
End Sub

     

    • Jn12345's avatar
      Jn12345
      Brass Contributor
      Nov 29, 2023
      Love it! thanks. Now this brings in one more question. Please see photo for what i did. It seems to work, however the issue is now basically if the button is already selected and i get the password wrong it switches to the wrong button. Is there a way to basically say "If the button is already selected then if the password is wrong just keep everything the same as well as if the password is true do nothing (since the button is already on the correct column) and if the button isnt selected then give me the option for what i just did? Sorry if thats confusing. Thanks so much!!!
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Nov 29, 2023

        Jn12345 

        If the option button was already True, clicking it will change it to False. So perhaps

        Sub ClockToDegrees()
    Dim Password As Variant
    
    If ActiveSheet.OptionButtons("Option Button 2").Value = True Then
        Password = Application.InputBox(Prompt:="Please Enter Password (WARNING: Date Will Be Cleared)", Type:=2)
        Select Case Password
            Case False
                'Nothing
            Case "FT007"
                ActiveSheet.Unprotect Password:="Protect123!"
                ' Change the name of the option button as needed
                ActiveSheet.OptionButtons("Option Button 1").Value = False
                ' More code here
                ' ...
                ActiveSheet.Protect Password:="Protect123!", DrawingObjects:=True, Contents:=True, Scenarios:=True
            Case Else
                MsgBox "The Password You Entered Is Incorrect", vbExclamation
        End Select
    End If
End Sub

Resources