Loading ComboBox From a Table

[Dezertdog]

Hi All,
I have a userform(DelForm) with a combobox(cbDelAssociate). I am loading the combo box with a list of names from a table on a worksheet("2015"). With this code:

 For x = 3 To ActiveSheet.Cells(1, 1).SpecialCells(xlLastCell).Row
               If ActiveSheet.Cells(x, 1) <> "" Then
                      DelForm.cbDelAssociate.AddItem ActiveSheet.Cells(x, 1)
               End If
    Next

The table starts on row 3.

This loads the names just fine but it takes a long time.

I have tried tweaking it but I can't seem to speed it up at all. Is this the wrong approach for what I am doing?

I use this code when the "Delete" button on the userform is clicked:

 Last = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row  
            For i = last To 1 Step -1

                  If (Cells(i, "A").Value) = DelForm.cbDelAssociate.Value Then

                        Cells(i, "A").EntireRow.Delete

                  End If

           Next i   

     cbDelAssociate.Value = "Click Arrow To Select-->"

This code works excellent and is instantaneous.
Then I call the code to load the combobox again (it takes forever again) but the name that was deleted still appears even though it has been removed from the Table.
So I am asking can one of you Gurus help me speed up the loading of the ComboBox. And remove the deleted name from the ComboBox after its removed from the table.

Thank you in advance. You have already helped me more than you know.

D-Dog<o></o>

 

[bill0145]

This is how i would do it.

Private Sub cbDelAssociate_DropButt*******()
Dim wks As Worksheet
Set wks = Worksheets("2015")
lr = Cells(Rows.Count, 1).End(xlUp).Row
For x = 3 To lr
    If wks.Cells(x, 1) <> "" Then
        DelForm.cbDelAssociate.AddItem wks.Cells(x, 1)
    End If
Next x
End Sub

Private Sub CommandButton1_Click()
Dim wks As Worksheet
Set wks = Worksheets("2015")
lr = Cells(Rows.Count, 1).End(xlUp).Row
For x = lr To 3 Step -1
    If Cells(x, 1) = DelForm.cbDelAssociate.Text Then
        Cells(x, 1).EntireRow.Delete
    End If
Next x
DelForm.cbDelAssociate.Clear
End Sub

 

[Dezertdog]

Thank you bill0145 for the quick reply, I will give that a shot tomorrow. Time to head home.
It is much appreciated!

D-Dog

 

[Rick Rothstein]

...but the name that was deleted still appears even though it has been removed from the Table

Based on the above, I think you are loading the list on top of the existing list (maybe even over and over again). Before filling a ComboBox (or ListBox for that matter) with a list of items, you should clear the ComboBox. I would put this statement just before your For..Loop...

DelForm.cbDelAssociate.Clear

 

[bill0145]

Rick has a great point, this might work better

Private Sub cbDelAssociate_Enter()
Dim wks As Worksheet
Set wks = Worksheets("2015")
DelForm.cbDelAssociate.Clear
lr = wks.Cells(Rows.Count, 1).End(xlUp).Row
For x = 3 To lr
    If wks.Cells(x, 1) <> "" Then
        DelForm.cbDelAssociate.AddItem wks.Cells(x, 1)
    End If
Next x
End Sub
Private Sub CommandButton1_Click()
Dim wks As Worksheet
Set wks = Worksheets("2015")
lr = wks.Cells(Rows.Count, 1).End(xlUp).Row
For x = lr To 3 Step -1
    If wks.Cells(x, 1) = DelForm.cbDelAssociate.Text Then
       wks.Cells(x, 1).EntireRow.Delete
    End If
Next x

 

[Dezertdog]

Rick has a great point, this might work better

Private Sub cbDelAssociate_Enter()
Dim wks As Worksheet
Set wks = Worksheets("2015")
DelForm.cbDelAssociate.Clear
lr = wks.Cells(Rows.Count, 1).End(xlUp).Row
For x = 3 To lr
    If wks.Cells(x, 1) <> "" Then
        DelForm.cbDelAssociate.AddItem wks.Cells(x, 1)
    End If
Next x
End Sub
Private Sub CommandButton1_Click()
Dim wks As Worksheet
Set wks = Worksheets("2015")
lr = wks.Cells(Rows.Count, 1).End(xlUp).Row
For x = lr To 3 Step -1
    If wks.Cells(x, 1) = DelForm.cbDelAssociate.Text Then
       wks.Cells(x, 1).EntireRow.Delete
    End If
Next x

bill0145, Thank you so much... The code works perfectly. I have a formula in cell P1 "YEAR(NOW())" and I have code in the WorkBook open module that creates a copy of the previous sheet with the current year on it. On January 1st. So I had to modify your code as follows:

Private Sub UserForm_Activate()
Dim wks As Worksheet
Dim MyWorksheetName As String
MyWorksheetName = ActiveSheet.Range("P1")
Set wks = Sheets(MyWorksheetName)
DelForm.cbDelAssociate.Clear
lr = wks.Cells(Rows.Count, 1).End(xlUp).Row
For x = 3 To lr
    If wks.Cells(x, 1) <> "" Then
        DelForm.cbDelAssociate.AddItem wks.Cells(x, 1)
    End If
Next x

 cbDelAssociate.Text = "Click Arrow To Select-->"
End Sub

And:

Private Sub DeleteButton_Click()
Dim wks As Worksheet
Dim MyWorksheetName As String
MyWorksheetName = ActiveSheet.Range("P1")
Set wks = Sheets(MyWorksheetName)
lr = wks.Cells(Rows.Count, 1).End(xlUp).Row
For x = lr To 3 Step -1
    If wks.Cells(x, 1) = DelForm.cbDelAssociate.Text Then
        wks.Cells(x, 1).EntireRow.Delete
    End If
Next x
Call UserForm_Activate
End Sub

I had to account for the variable sheet name. But as I said... Works great! Thanks again.

And Rick, Thank you for that little tidbit.. It really helped me to understand what was happening with the code I had.

You guys ROCK!
D-Dog