Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

combobox list - fill with data across columns and remove blanks

Posted on 2013-02-04
7
Medium Priority
?
701 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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

972 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