Solved: Update drop down view list

[av8tordude]

I have this code in a combo box that deletes the name from a list in column BI. It works fine except, in the drop down view it still displays the name. The only way it gets updated is I unload the userform. How can I update the drop down view list without unloading the userform?

[VBA]Dim lRow
If MsgBox("Delete '" & cboName & "' from the database?", vbYesNo + vbQuestion, "PerDiem Traveler v" & Sheets("Security").Range("J2")) = vbYes Then
On Error Resume Next
lRow = Range("BI11", Range("BI" & Rows.Count).End(xlUp)) _
.Find(what:=cboName.Value, lookat:=xlWhole).Row
On Error GoTo 0
Range("BI" & lRow).Resize(, 4).ClearContents
MsgBox cboName & " has been deleted.", vbInformation, "PerDiem Traveler v" & Sheets("Security").Range("J2")
Range("BI11:BL500").Sort Key1:=Range("BI11"), Order1:=xlAscending, Header:=xlNo
Del2.Visible = False
End If
[/VBA]

[jolivanes]

Would Repaint work?

[Bob Phillips]

How is the data loaded into the dropdown (combobox?)?

[av8tordude]

This list is loaded via this code...

[VBA]With ActiveSheet
lastrow = .Cells(.Rows.Count, "BI").End(xlUp).Row
ReDim vecMerchants(1 To lastrow - 10)
For i = 11 To lastrow
If IsError(Application.Match(.Cells(i, "BI").Value, vecMerchants, 0)) Then
idxMerchants = idxMerchants + 1
vecMerchants(idxMerchants) = .Cells(i, "BI").Value
End If
Next i
ReDim Preserve vecMerchants(1 To idxMerchants)
cboName.List = vecMerchants
End With
[/VBA]

[Bob Phillips]

Any chance of the workbook to play with?

[av8tordude]

See attached

[Kenneth Hobs]

Rather than Activate, I would use Initialize. Putting the routine to fill it in a separate routine, you can call it to refill it.

[VBA]Private Sub Del1_Click()
Dim lRow

If MsgBox("Delete '" & cboName & "' from the database?", vbYesNo + vbQuestion) = vbYes Then
On Error Resume Next
lRow = Range("BI11", Range("BI" & Rows.Count).End(xlUp)) _
.Find(what:=cboName.Value, lookat:=xlWhole).Row
On Error GoTo 0
Range("BI" & lRow).Resize(, 4).ClearContents
MsgBox cboName & " has been deleted.", vbInformation
Range("BI11:BL500").Sort Key1:=Range("BI11"), Order1:=xlAscending, Header:=xlNo
cboName.Clear
UpdateMerchants
End If
End Sub

Private Sub UpdateMerchants()
Dim rRange As Range
Dim vecMerchants As Variant
Dim idxMerchants As Long
Dim lastrow As Long
Dim i As Long

On Error Resume Next
With ActiveSheet
lastrow = .Cells(.Rows.Count, "BI").End(xlUp).Row
ReDim vecMerchants(1 To lastrow - 10)
For i = 11 To lastrow
If IsError(Application.Match(.Cells(i, "BI").Value, vecMerchants, 0)) Then
idxMerchants = idxMerchants + 1
vecMerchants(idxMerchants) = .Cells(i, "BI").Value
End If
Next i
ReDim Preserve vecMerchants(1 To idxMerchants)
cboName.List = vecMerchants
End With
End Sub

Private Sub UserForm_Initialize()
UpdateMerchants
End Sub[/VBA]

[av8tordude]

Thank you Ken. :-)