Create combobox .list excluding some columns

**Hammer_757** · 12-28-2012, 03:24 PM

I have a Combobox that is created based on a named range of column headings. Starting with the first column, every other column is titled “ (required) “. I do not want the “ (required) “ Headings in the Combobox. Any suggestions on how to do this?
Problem area is in the last few lines few code

Private Sub UserForm_Initialize()

Dim lastRow As Integer
Dim emplArr
Dim frmTop As Integer
Dim startRowCnt As Integer
Dim endRowCnt As Integer
Dim holder As String
Dim chbxTmp As Control
Dim topicRng As Range


 lastRow = Range(Range("b2"), Range("b2").End(xlDown)).Count + 1

  frmTop = 80
  emplArr = Range("B2:B" & lastRow)
  
  'list the names
  With lstAttendees
        .List = Range(Range("b2"), Range("b2").End(xlDown)).Value
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
  End With

  
  Me.Height = (lastRow - 5) * 20 + 180
    Me.ScrollHeight = Me.Height
  Me.Height = Me.Height * 0.5
  Me.ScrollBars = fmScrollBarsVertical
  
  'PROBLEM HERE
  'setup Topic Listbox
    Set topicRng = Range("topics")
    cbx_Topic_Choice.List = Application.WorksheetFunction.Transpose(topicRng)

End Sub

File attached if that helps.
The code is initialized by the "Update Topics" button at the bottom of the data on the first worksheet.

Thanks
Robert

**jaslake** · 12-28-2012, 05:13 PM

Hi Hammer_757
Try this

'setup Topic Listbox
    For Each topic In Range("topics")
        If Not topic.Value = "(Required)" Then
            With Me.cbx_Topic_Choice
                .AddItem topic.Value
            End With
        End If
    Next topic

**HSV** · 12-28-2012, 05:51 PM

Or:

End With

  
  Me.Height = (lastRow - 5) * 20 + 180
  
  Me.ScrollHeight = Me.Height
  Me.Height = Me.Height * 0.5
  Me.ScrollBars = fmScrollBarsVertical
  
  
  'setup Topic Listbox
  With Sheets("2012 4th LOC SHOP")
    .Range(.Cells(1, 6), .Cells(1, .Cells(1, Columns.Count).End(xlToLeft).Column)).Name = "topics"
   End With
  cbx_Topic_Choice.List = Split(Join(Filter([if(topics <>"(required)",topics,"~")], "~", False), "|"), "|")
End Sub