When deleting the last item in the list box an error message appears giving me a "Type Mismatch - Could not set the value property" Error. Deleting any other item in the listbox there is no problem.
The code is:
me.txtAccountNumber.Value = Application.VLOOKUP(me.lstAccountName, Sheets("AcctNames").Range ("A:B"), 2, False)
Any help would be appreciated and thank you in advance
Is one of the list items selected when you delete the last entry?
You can check the .ListIndex property to ensure the default property of lstAccountName has a valid value. It should be a value between zero and listcount-1
I check the last record isn't deled the coding, by replacing the "+" with a "-", the last record isn't deleted
Private Sub cmdDeleteAccount_Click()
Dim strDeleteResponse As String
Dim lngIndex As Long
Application.ScreenUpdating = False
Sheets("AcctNames").Visible = True
Sheets("AcctNames").Activate
strDeleteResponse = MsgBox("This will delete the selected row. Are you sure you want to remove this transaction?", vbCritical + vbYesNo, "Delete Entry")
Select Case strDeleteResponse
Case vbYes
lngIndex = Me.lstAccountName.ListIndex + 1
If lngIndex <> -1 Then
AcctNames.Rows(lngIndex).EntireRow.Delete
Me.lstAccountName.RowSource = "a:a"
End If
Case vbNo
Exit Sub
End Select
Application.ScreenUpdating = False
Sheets("AcctNames").Visible = True
Sheets("AcctNames").Activate
End Sub
From start to finnish here is the code for my delete record.
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
Are you really listing all of column A?
Perhaps it would make more sense if you posted workbook example.
CODE=
Private Sub cmdDeleteAccount_Click()
Dim strDeleteResponse As String
Dim lngIndex As Long
Application.ScreenUpdating = False
Sheets("AcctNames").Visible = True
Sheets("AcctNames").Activate
strDeleteResponse = MsgBox("This will delete the selected row. Are you sure you want to remove this transaction?", vbCritical + vbYesNo, "Delete Entry")
Select Case strDeleteResponse
Case vbYes
lngIndex = Me.lstAccountName.ListIndex + 1
If lngIndex <> -1 Then
AcctNames.Rows(lngIndex).EntireRow.Delete
Me.lstAccountName.RowSource = "a:a"
End If
Case vbNo
Exit Sub
End Select
Application.ScreenUpdating = False
Sheets("AcctNames").Visible = True
Sheets("AcctNames").Activate
End Sub
CODE/
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks