Code: Select all
Private Sub cmdRemove_Click()
Const METHODNAME As String = "cmdRemove_Click"
Dim lsStatus As String
Dim lsSheet As String
Dim lsRange As String
Dim liListIndex As Integer
Dim wsht As Worksheet
Dim qt As QueryTable
Dim found As Boolean
Dim li As Variant
On Error GoTo errorhandler
If lbxMain.ListIndex < 1 Then
Exit Sub
End If
Dim vbRet As VbMsgBoxResult
Dim msg As String
vbRet = MsgBox("Clear data as well?", vbYesNoCancel Or vbDefaultButton2 Or vbQuestion, "Confirm")
If vbRet = vbCancel Then
GoTo exithandler
End If
Dim cnt As Integer
cnt = lbxMain.ListCount
'first get a list of all selected items...
Dim selected As New Collection
For liListIndex = cnt To 1 Step -1
If lbxMain.selected(liListIndex) Then
selected.Add liListIndex
End If
Next
For Each li In selected
lsRange = lbxMain.List(li, 1)
Set qt = GetQT(lsRange, wsht)
If Not qt Is Nothing Then
If vbRet = vbYes Then
qt.ResultRange.Clear
End If
'because this qt might be under a listobject,
'we'll call a common routine to delete it
Call DeleteQT(wsht, qt)
lbxMain.RemoveItem li
End If
Next
exithandler:
On Error Resume Next
Exit Sub
errorhandler:
Dim res As VbMsgBoxResult
res = EV_DisplayError(METHODNAME, lsStatus, Err.Description, Err.Number)
Select Case res
Case vbRetry
Stop
Resume
Case vbAbort
Resume exithandler
Case vbIgnore
Resume Next
End Select
End Sub