Combobox additem

jag108

Active Member
Joined
May 14, 2002
Messages
429
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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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
429
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
429
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
429
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
429
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,082
Messages
5,570,097
Members
412,311
Latest member
Mozz
Top