CODEWITHSUNDEEP
vbaexcel
D. Morley
D. Morley

Reputation: 73

VBA Macro With Password Still Operates When Entered Incorrectly

I have a VBA macro in an Excel sheet that refreshes a SQL query but is password protected--or so I thought. How the macro should work is when you click the button, a password prompt appears and if entered correctly, the query refreshes. If entered incorrectly, nothing should happen.

What I recently discovered is that even if someone enters the password incorrectly, the query refreshes anyways. What would I enter into my code to stop the macro from running if the password is incorrect?

Sub Button1_Click()

Dim password As Variant
 password = Application.InputBox("Enter Password", "Password Protected")

Select Case password
     Case Is = False
         'do nothing
     Case Is = "Select15"
         Range("A1").Value = "Code"
     Case Else
         MsgBox "Incorrect Password"
End Select


 For Each sh In Worksheets
 If sh.FilterMode Then sh.ShowAllData
 Next

ActiveWorkbook.RefreshAll


End Sub

Upvotes: 1

Views: 1291

Answers (2)

Vityata
Vityata

Reputation: 43575

Sub Button1_Click()

Dim password As Variant
 password = Application.InputBox("Enter Password", "Password Protected")

Select Case password
     Case Is = False
         'do nothing
          exit sub
     Case Is = "Select15"
         Range("A1").Value = "Code"
     Case Else
         MsgBox "Incorrect Password"
         exit sub
End Select


 For Each sh In Worksheets
 If sh.FilterMode Then sh.ShowAllData
 Next

ActiveWorkbook.RefreshAll


End Sub

You should exit the sub, if the password is not the correct one. --^^

Or you can create a boolean bCorrect, to keep the result of the password and exit, if it is false.--v

Sub Button1_Click()

    Dim password As Variant
    Dim bCorrect As Boolean

    password = Application.InputBox("Enter Password", "Password Protected")

    Select Case password

    Case Is = False
        'do nothing
    Case Is = "Select15"
        bCorrect = True
        Range("A1").value = "Code"
    Case Else
        MsgBox "Incorrect Password"
    End Select

    If Not bCorrect Then Exit Sub

    For Each sh In Worksheets
        If sh.FilterMode Then sh.ShowAllData
    Next

    ActiveWorkbook.RefreshAll

End Sub

Upvotes: 3

Scott
Scott

Reputation: 3732

Any value your user enters (except -1) is coded as "False", so your first case is being triggered. Check if the password is correct as your first option.

Upvotes: 0

Related Questions