Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Button Macro not to be pressed unless a password is provided

Copper Contributor

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 though it wont run the macro) please see attached photos for more detail. Im wondering if there is a way to make it so that the button doesnt click at all unless the password was correct. The easiest solution is probably to only use a normal button but Im hoping to use the option button i have selected. Please view photos from 1-5

8 Replies

@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...

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

@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

 

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

@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

@Hans Vogelaar 

 

Im trying to make it so that if the button is already filled in as true that if i then click the same button then nothing happens so that the only option is to select a button that is false or empty from the fill. so the button on the left is 2 and the one on the right is 3. so if im trying to input into the column under button two if 2 is already selected its pointless so nothing happens but if button three was already selected then i could select button two to make it run the code. Unfortunately when i use this code it breaks the button and neither macros run and the button simply goes from on to off with no macro being run

@Jn12345 

I'm afraid you've lost me, I'm not sure it is possible to do what you want with option buttons.