PDA

View Full Version : Solved: Update drop down view list



av8tordude
02-12-2012, 09:31 PM
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?

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

jolivanes
02-12-2012, 10:44 PM
Would Repaint work?

Bob Phillips
02-13-2012, 04:45 AM
How is the data loaded into the dropdown (combobox?)?

av8tordude
02-13-2012, 09:08 AM
This list is loaded via this code...

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

Bob Phillips
02-13-2012, 10:01 AM
Any chance of the workbook to play with?

av8tordude
02-13-2012, 12:52 PM
See attached

Kenneth Hobs
02-13-2012, 01:11 PM
Rather than Activate, I would use Initialize. Putting the routine to fill it in a separate routine, you can call it to refill it.

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

av8tordude
02-13-2012, 02:04 PM
Thank you Ken. :-)