Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

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.
Sampledata.xlsm
0
Michael Keith
Asked:
Michael Keith
  • 2
  • 2
1 Solution
 
FlysterCommented:
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.

Flyster
Sampledata.xlsm
0
 
terencinoCommented:
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...
Terry
Sub getTitles()
Dim c As Range
Dim oSheet As Excel.Worksheet
Dim oCmbBox As MSForms.ComboBox
Set oCmbBox = ActiveWorkbook.Sheets("Snapshot").ComboBox1
oCmbBox.Clear
For Each c In ActiveWorkbook.Sheets("Outstanding Debt ").UsedRange.Rows(15).Cells
  If c.Value = "" Then GoTo Skip
  oCmbBox.AddItem c.Value
Skip:
Next c
Debug.Print titles
End Sub

Open in new window

0
 
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.
Sampledata.xlsm
0
 
terencinoCommented:
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.
Sampledata2.xlsm
0
 
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.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now