Vab code Delete button

[Sacruzsa]

Hi guys

I need your help for the following problem:

I have created a form for quick retrieval of phone numbers and email addresses, everything works except for the delete button
I want to protect this with a password so that it cannot be accidentally deleted, but there is something wrong with my code.
When I press the delete button it asks for a password and when I press cancel the field is deleted anyway, hopefully someone of you can help me

code:
Private Sub CommandButton5_Click()

Dim password As Variant

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

Select Case password

Case Is = False
'do nothing

Case Is = "23BnMed"

Case Else

MsgBox "The password you entered was incorrect"

End Select

If Me.ListBox1.ListIndex >= 0 Then
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("AddressBook")

Dim myRow As Long
myRow = Application.WorksheetFunction.Match(Me.ListBox1.List(Me.ListBox1.ListIndex, 0), sh.Range("A:A"), 0)

sh.Range("A" & myRow).EntireRow.Delete

Call Refresh_List_Box

MsgBox "Record has been deleted", vbInformation

End If

End Sub

 

[Micron]

End Sub after msgbox otherwise there is nothing to stop the rest of the code from running. I cannot see how the return value can ever be False, so it's really either the password value you want, or it's not. If it's provided but incorrect, your Case Else will come into play. If it's not provided the return value is a zls (zero length string) so it's still not. Might as well just use an IF block? I might write it thus (please use vba code tags for code - vba button on posting toolbar) to maintain indentation and improve readability:

Private Sub CommandButton5_Click()
Dim password As Variant
Dim sh As Worksheet

If Not Application.InputBox("Please Enter Password", "Password Protected Macro") = "23BnMed" Then
     MsgBox "Incorrect password entered."
     Exit Sub
End If

Dim myRow As Long
Dim sh As Worksheet

If Me.ListBox1.ListIndex >= 0 Then
   Set sh = ThisWorkbook.Sheets("AddressBook")
   myRow = Application.WorksheetFunction.Match(Me.ListBox1.List(Me.ListBox1.ListIndex, 0), sh.Range("A:A"), 0)
   sh.Range("A" & myRow).EntireRow.Delete
   Refresh_List_Box
   MsgBox "Record has been deleted", vbInformation
End If
Set sh = Nothing

End Sub

I would declare all variables rather than just some. IMO you should have Option Explicit at the top of every code module, and in fact, set that as an option going forward. If interested, find "require variable declaration" option and turn it on for new modules.

 

[Sacruzsa]

End Sub after msgbox otherwise there is nothing to stop the rest of the code from running. I cannot see how the return value can ever be False, so it's really either the password value you want, or it's not. If it's provided but incorrect, your Case Else will come into play. If it's not provided the return value is a zls (zero length string) so it's still not. Might as well just use an IF block? I might write it thus (please use vba code tags for code - vba button on posting toolbar) to maintain indentation and improve readability:

Private Sub CommandButton5_Click()
Dim password As Variant
Dim sh As Worksheet

If Not Application.InputBox("Please Enter Password", "Password Protected Macro") = "23BnMed" Then
     MsgBox "Incorrect password entered."
     Exit Sub
End If

Dim myRow As Long
Dim sh As Worksheet

If Me.ListBox1.ListIndex >= 0 Then
   Set sh = ThisWorkbook.Sheets("AddressBook")
   myRow = Application.WorksheetFunction.Match(Me.ListBox1.List(Me.ListBox1.ListIndex, 0), sh.Range("A:A"), 0)
   sh.Range("A" & myRow).EntireRow.Delete
   Refresh_List_Box
   MsgBox "Record has been deleted", vbInformation
End If
Set sh = Nothing

End Sub

I would declare all variables rather than just some. IMO you should have Option Explicit at the top of every code module, and in fact, set that as an option going forward. If interested, find "require variable declaration" option and turn it on for new modules.

Hi Micron,
thanks for the help, now it works perfectly

 

[Micron]

You're welcome. Perhaps mark this one as solved then?

 

[Micron]

FOR FUTURE READERS - in my first post I meant to write Exit Sub, not End Sub. Unfortunately I cannot revise now.