Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

combobox list - fill with data across columns and remove blanks

Posted on 2013-02-04
7
Medium Priority
?
682 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

721 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