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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

jag108

Active Member
Joined
May 14, 2002
Messages
433
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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.

:(
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Confusing

Which column are you adding to the combobox under what condition?
 

jag108

Active Member
Joined
May 14, 2002
Messages
433
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Sorry, but I don't think you are answering to my question.

Which column are you adding to the combobox?
 

jag108

Active Member
Joined
May 14, 2002
Messages
433
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Sorry column "C"
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Is this what you mean?
Code:
If Not IsEmpty(cell) And Not IsEmpty(cell.Offset(, -1)) Then
            .AddItem cell.Value
        End If
 

jag108

Active Member
Joined
May 14, 2002
Messages
433
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
No this is still picking up the blanks as well.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,996
Messages
5,834,784
Members
430,322
Latest member
excelnoobnoob

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