wrong password counter

[wasntme]

Hi,

I have written simple date check on open in one of my files, that prompts for password when it is expired and want to add wrong password counter and will add some more code to it on lets say 3 wrong attempts, but can not manage it to work.

Private Sub Workbook_Open()
Dim pass As Variant
Dim exdate As Date


exdate = "01/06/2019"


If Date > exdate Then
      pass = Application.InputBox("Please enter password")
      Select Case pass
    
       Case Is = "yes"
         
       Case Else
                 
         MsgBox "Oopsie"
         Application.DisplayAlerts = False
         ActiveWorkbook.Close
         
      End Select


End If
End Sub

Any help is greatly appreciated, thanks.

 

[sykes]

Something like this, perhaps...

Private Sub Workbook_Open()

Dim pass As Variant
Dim exdate As Date
Dim cntr As Integer


exdate = "01/06/2019"
cntr = 1

Do While cntr < 4
If Date > exdate Then
      pass = Application.InputBox("Please enter password", "Password please")

If pass = "yes" Then Exit Sub
         MsgBox "Oopsie - wrong password; you have " & 3 - cntr & " attempt(s) left!", 48, "Wrong password!"

End If
cntr = cntr + 1
Loop
End Sub

 

[wasntme]

Hi sykes, thank you for your input. I have managed to get this result using case scenarios, but it is not exactly what i wanted now when i put more thought in it . And of course i have not formulated my question correctly.

I was kinda hoping to get counter that will not reset every time file is opened. I can use a dedicated cell in the file for this purpose, but maybe there is a way to do it in another way in VBA only?

On a side note what does this part do?

, 48,

 

[wasntme]

Heh, by testing i have managed to answer my last question about 48. Learning is a wonderful thing

 

[sykes]

Hi. Yes - you could use a spare, remote cell somewhere, to keep the number of attempts recorded (your code would need to have a workbook_save line in it, too).
One of the troubles with this kind of "protection", is always that the users can disable macros from running in their Excel application, and bypass the password "protection" anyway, so it has limited value, to be honest.
You also need to remember to password-protect your VBA project, otherwise, a VBA-savvy user can just open your code, and read the hard-coded password!

The "48" is one of the msgbox "constants" which tell VBA what type of buttons you wish to display when it shows (Yes/no, or just "OK" etc), and any other characteristics you want - like an exclamation mark, or question mark.
There's a list of msgbox constants Here:
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/msgbox-constants
You can select the ones you want, then add them together, and use that figure in your code - so if you wanted the red warning "cross" along with both "OK" and "Cancel" buttons, you'd use 16 + 1 = 17, instead of my 48.

HTH

 

[sykes]

Ah, our posts crossed!
Yes - learning is indeed, wonderful - and what a great site this is, from which to learn!

 

[wasntme]

Many thanks for the info, slowly getting the desired result and learning new things

Yes, i have added VBA password, but indeed i am worried of opening the file with disabled macros. Any directions you can point me at to get stronger protection?

 

[sykes]

You could have the worksheets you only want certain users to see, Very Hidden (so that a user selecting "Unhide" still won't see them. Then use code to unhide & re-hide them for those "Trusted" users.
That way, anyone opening the workbook without code running, can't see your Treasured sheets, but those running code can - if you choose to let them.

Worksheets("Sheet1").Visible = xlVeryHidden

Worksheets("Sheet1").Visible = True

...probably best in the workbook's "Before_Close" event, to re-hide all the sheets you want protecting, and remember to save...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Sheet1").Visible = xlVeryHidden
Me.Close (True)
End Sub

 

[wasntme]

Thanks, i have similar functionality in other files, just need to be creative using it.

Once again, thank you for your time. Hard to advance when there is no one even slightly familiar with VBA in the office

 

[sykes]

...but the upside is, that makes you the office VBA Guru!!
Pleasure to help.