combobox list - fill with data across columns and remove blanks

Attached is the spreadsheet that I am working with.   I want grab the titles in row 15 across different columns on the outstanding debt worksheet and populate a combobox to create a selection list and remove any blanks.   The combobox is on the snapshot page.   I have tried to figure this out with no luck.    hoping to get some ideas on how to best approach this.
Michael KeithAsked:
Who is Participating?
terencinoConnect With a Mentor Commented:
Yes the problem is in the event code:
Private Sub ComboBox1_DropButtonClick()
Call getTitles
End Sub

Open in new window

Just delete the Call getTitles line and it will work fine. The trouble with the sheet-level ActiveX controls like this combobox is they have no initialize function (which you could use to populate the box), so maybe you could use a different event like Workbook_Open to populate it when the workbook is opened, or a Worksheet_Change event on the Outstanding Debt sheet, to re-populate the box if the  titles are changed. If the titles don't change, the box will just remain populated after the macro is run. Does that make sense?

Another option is to eliminate the macro, and use a named range with the titles (listed just by linking to the title cell on the Outstanding Debt sheet) as the ListFillRange property of the combobox. I've updated this sheet to show you how that works.
I don't know if this is the "best" approach or not, but this is what I did. I copied the titles in row 15 and then went to cell O1 in the Snapshot tab and pasted special - transposed. Then I went to the data tab and selected remove Next, in design mode, I highlighted the combo box and selected properties. Next to ListFillRange I entered O1:O10. I then hid column O.

Hi this might help also...
References the combobox, clears existing entries, cycles through all the cells in row 15 of Outstanding Debt, then adds them to the combobox list. Note that Outstanding Debt has a space on the end of the sheet name...
Sub getTitles()
Dim c As Range
Dim oSheet As Excel.Worksheet
Dim oCmbBox As MSForms.ComboBox
Set oCmbBox = ActiveWorkbook.Sheets("Snapshot").ComboBox1
For Each c In ActiveWorkbook.Sheets("Outstanding Debt ").UsedRange.Rows(15).Cells
  If c.Value = "" Then GoTo Skip
  oCmbBox.AddItem c.Value
Next c
Debug.Print titles
End Sub

Open in new window

Michael KeithAuthor Commented:
Terry -

I have been playing around with what you provided and it works except when I select something from the dropdown it wants to clearout and does not keep the selection.   Can you provide any help with this or tell me what I am doing wrong.
Michael KeithAuthor Commented:
I will take a look at this today and let you know how it goes or if I have any additional questions.   Thanks again for your help on this.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.