Solved

combobox list - fill with data across columns and remove blanks

Posted on 2013-02-04
7
606 Views
Last Modified: 2013-09-25
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
Comment
Question by:Apex623
  • 2
  • 2
7 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 38853528
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
 
LVL 16

Expert Comment

by:terencino
ID: 38853576
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
 
LVL 1

Author Comment

by:Apex623
ID: 38868902
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
 
LVL 16

Accepted Solution

by:
terencino earned 500 total points
ID: 38870349
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
 
LVL 1

Author Comment

by:Apex623
ID: 38876696
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now