Combobox additem

jag108

Active Member
Joined
May 14, 2002
Messages
433
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
I have a combo box which is populated via a for loop, from data on a work sheet. The problem I am having is that there are a number of empty entries in the top of the combo box, so you have to scroll down about 10 or so before you can see any data.

Code:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Activate()
<SPAN style="color:#00007F">Set</SPAN> src_sht = Worksheets("Channel Details")
src_sht.Select
<SPAN style="color:#00007F">Set</SPAN> TestRange = Intersect(Range("B:B"), ActiveSheet.UsedRange)
lastrow = TestRange.Cells(TestRange.Cells.Count).Row <SPAN style="color:#007F00">'get last row number in the range</SPAN>
firstrow = TestRange.Cells(1).Row <SPAN style="color:#007F00">'get the first row number in the range</SPAN>
<SPAN style="color:#00007F">With</SPAN> Channel_events.ComboBox1
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> src_sht.Range(Cells(2, 3), Cells(lastrow, 3))
        <SPAN style="color:#00007F">If</SPAN> cell.Offset(0, 1).Value = <SPAN style="color:#00007F">Empty</SPAN> <SPAN style="color:#00007F">Or</SPAN> cell.Offset(0, 1).Value = " " <SPAN style="color:#00007F">Then</SPAN>
        
        <SPAN style="color:#00007F">Else</SPAN>
            .AddItem cell.Value
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Worksheets("Events").Select
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

What this does is look at the adjacent cell if it is empty then do nothing, other wise populate the combo box with the data.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi
change
Code:
With Channel_events.ComboBox1
    For Each cell In src_sht.Range(Cells(2, 3), Cells(lastrow, 3))
        If Not IsEmpty(cell) And Not IsEmpty(cell.Offset(0, 1)) Then
            .AddItem cell.Value
        End If
    Next
End With
 
Upvote 0
Hi,

I tried that but it failed, I have also tried your code but failure again.

The data is in cols b and c, and has blanks in the C column, which I want to exclude.

:(
 
Upvote 0
OK - I will try and explain a little better.
I have TV channels that I need to extract data for, the chanel codes are in another sheet so this is used to ppopulate the combo box.
IBMS_CA_Template_Parameters.xls
BCDE
1CHANNEL_NAMETRANSMISSION_CODESI_SERVICE_KEYCHANNEL_SHORT_CODE
2APromoPriorities0A8
3ASPORTSPromoPriorities0A9
4AnimalPlanetANIM70715
5ApnaFMAPNA61287
6BBCWorldBBCV70461
7BusinessLink508BL
8CalvaryChapelCCRC42496
9CartoonNetworkCRTN302C1
10CCTV9CCTV208C2
11Ch65075
12Channel10CH1
13ChannelZEDGE13060
14CNNIntDBSCNNS70506
15ConcertFMRNZ232252
16DiscoveryDISC20408
17DiscoveryUHF80028
18DisneyChannelDISN70898
19EECHL207E2
20ESPNInternationalESPN50512
Channel Details


The &nbsp: are blank cells, cells that are not wanted I only want the cells with data in the combo box. I hope this helps.
 
Upvote 0
Sorry, but I don't think you are answering to my question.

Which column are you adding to the combobox?
 
Upvote 0
Is this what you mean?
Code:
If Not IsEmpty(cell) And Not IsEmpty(cell.Offset(, -1)) Then
            .AddItem cell.Value
        End If
 
Upvote 0
No this is still picking up the blanks as well.
 
Upvote 0
Code:
Private Sub UserForm_Activate()

Set src_sht = Worksheets("Channel Details")
src_sht.Select
Set testrange = Intersect(Range("C:C"), ActiveSheet.UsedRange)
lastrow = testrange.Cells(testrange.Cells.Count).Row 'get last row number in the range
firstrow = testrange.Cells(1).Row 'get the first row number in the range
With Channel_events.ComboBox1
    For Each cell In src_sht.Range(Cells(2, 3), Cells(lastrow, 3))
        If Not IsEmpty(cell) And Not IsEmpty(cell.Offset(, -1)) Then
            .AddItem cell.Value
        End If
    Next
End With
Worksheets("Events").Select
End Sub
 
Upvote 0

Forum statistics

Threads
1,217,482
Messages
6,136,905
Members
450,030
Latest member
Adalinda

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
Back
Top