add item in combobox without duplicated or blanks with table

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,097
Office Version
  1. 2010
hello
I have this code but the problem shows the duplicated items or blanks so what I would modified and doesn't show duplicated or blanks items
VBA Code:
Private Sub UserForm_Initialize()
    Const COL_NUM As Integer = 2 ' Which column you want to access '

    Dim ws As Worksheet
    Dim i As Long
    Dim tbl As ListObject

    Set ws = sheet5

    Set tbl = ws.ListObjects("CustInfo")

    ComboBox1.Clear

    With tbl
       ' Add cell values one at a time to the Combo Box List
       For i = 1 To tbl.DataBodyRange.Rows.Count
           ComboBox1.AddItem tbl.DataBodyRange.Cells(i, COL_NUM)
       Next
    End With

end sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
    With CreateObject("scripting.dictionary")
       ' Add cell values one at a time to the Combo Box List
       For i = 1 To tbl.DataBodyRange.Rows.Count
         If tbl.DataBodyRange.Cells(i, COL_NUM) <> "" Then
            .Item(tbl.DataBodyRange.Cells(i, COL_NUM)) = Empty
         End If
       Next
       Me.ComboBox1.List = .Keys
    End With
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,097
Office Version
  1. 2010
hi
it 's empty the combobox doesn't show anything
this is the code
VBA Code:
Private Sub UserForm_Initialize()
    Const COL_NUM As Integer = 2 ' Which column you want to access '
    Dim ws As Worksheet
    Dim i As Long
    Dim tbl As ListObject
    Set ws = sheet5
    Set tbl = ws.ListObjects("CustInfo")
    ComboBox1.Clear

    With CreateObject("scripting.dictionary")
       ' Add cell values one at a time to the Combo Box List
       For i = 1 To tbl.DataBodyRange.Rows.Count
         If tbl.DataBodyRange.Cells(i, COL_NUM) <> "" Then
            .Item(tbl.DataBodyRange.Cells(i, COL_NUM)) = Empty
         End If
       Next
       Me.ComboBox1.List = .Keys
    End With
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
            .Item(tbl.DataBodyRange.Cells(i, COL_NUM).Value) = Empty
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,143,637
Messages
5,719,969
Members
422,252
Latest member
wannabegeek1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top