Question

Fill a ComboBox from a Column

Asked by: RWayneH

I have a sheet that get populated dynamically, getting a number of entries throughout the day.  I am creating a userform that has a combobox in it that I would like to populate with that specific set of entries.  The specific's to that list are:
1) It will always start on cell D7.
2) No duplicate entries.
3)Searching down the list to the first blank cell will not work and I have to offset the combobox list two cell up from that blank cell.  That would be the bottom of the list range, top being cell D7.
4) If the sheet is blank, and a list cannot be found need to exit with messagebox.  This may happen when two cells up from that bottom blank cell has a cell name of: OneCellUpFromD7   which is the column header

There are some other things I would like to do after a selection is made from the combox but I first need to populate it.

Doing these dynamic named ranges kills me... especially when you have to offset it a few cells up from that bottom marker.
Please advise and thanks.  -R-

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-11-05 at 07:54:22ID24874837
Topic

Microsoft Excel Spreadsheet Software

Participating Experts
1
Points
500
Comments
19

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. VBA; Excel; population a ComboBox on UserForm
    Hi there, I would like to populate a Combobox on a Userform with items. I want to use the content of column A of my spreadsheet as items. I know the first item will be the value of cell A1. The problem is that the last used cell of column A is unknown, since this changes ...
  2. Combobox with no userform
    I can create a userform with a combobox just fine but i'm having trouble creating a userform directly in my worksheet. Is there something i'm missing? I'm orginally wanting to have the combobox selected and when I click on another button to go to a specific page I want the i...
  3. Excel Userform - Autopopulate a column based on a Comb…
    Please help! I have reached an impasse. I have created a UserForm for data entry in excel. I have created a three column combobox which a user can choose a the first name / last name / ID. This combobox was created with named fields in the worksheet. Now I need a textbox...
  4. Userform
    I have a userform that has a combobox that is looking at column A. Is it possible in VBA to populate 2 other fields that are on my userform when I make a selection in the combo box. I want it to report back columns B and C after I select the choice from the combobox.

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: patrickabPosted on 2009-11-05 at 09:48:28ID: 25751973

RWayneH,

The code below is in the attached file. Press the button to see the Userform & ListBox.

Hope it helps

Patrick

Private Sub CommandButton1_Click()
UserForm1.Hide
Unload UserForm1
End Sub
 
Private Sub ListBox1_Click()
Dim i As Long
 
For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) = True Then
        Sheets("Sheet1").[A1] = Me.ListBox1.List(i)
    End If
Next i
End Sub
 
Private Sub UserForm_Initialize()
Dim rng As Range
Dim celle As Range
Dim coll As New Collection
Dim i As Long
 
With Sheets("Sheet1")
    Set rng = Range(.Cells(7, "D"), .Cells(.Cells.Rows.Count, "D").End(xlUp))
End With
 
For Each celle In rng
    On Error Resume Next
    coll.Add CStr(celle), CStr(celle)
Next celle
 
For i = 0 To coll.Count
    Me.ListBox1.AddItem --coll(i)
Next i
 
End Sub

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:

Select allOpen in new window

 

by: patrickabPosted on 2009-11-05 at 09:54:33ID: 25752033

RWayneH,

You may find the comments in the code helpful...

Patrick

Private Sub ListBox1_Click()
Dim i As Long
 
'when an item is selected in the ListBox, loop through all the items until the item is
'identified as selected, then output it to cell A1 on Sheet1
For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) = True Then
        Sheets("Sheet1").[A1] = Me.ListBox1.List(i)
    End If
Next i
End Sub
 
Private Sub UserForm_Initialize()
Dim rng As Range
Dim celle As Range
Dim coll As New Collection
Dim i As Long
 
'define the range with the data
With Sheets("Sheet1")
    Set rng = Range(.Cells(7, "D"), .Cells(.Cells.Rows.Count, "D").End(xlUp))
End With
 
'create a Collection of unique items
For Each celle In rng
    On Error Resume Next
    'only strings can be added to the Collection
    coll.Add CStr(celle), CStr(celle)
Next celle
 
'add all the items in the Collection to the ListBox
For i = 0 To coll.Count
    'convert the numerical string back to numbers by using --
    Me.ListBox1.AddItem --coll(i)
Next i
 
End Sub

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:

Select allOpen in new window

 

by: RWayneHPosted on 2009-11-05 at 10:23:38ID: 25752312

Couple things.  I need to save one line in table.  I made some edits to your sample file where the userform should fail to show.  Stating something like "There are no valid records, please add one". then Exit Sub.  It is the part in the request that refers to the cell name on Sheet1 called: OneCellUpFromD7.

The only other thing that I wanted to do was after a selection was made from the userform>combox (row), that the whole row is cut out of Sheet1 (shifting cells up) and inserted in Sheet2, right at row 7 shifting all cells down.

Think of it as, Sheet1 as a working sheet and Sheet2 is HistoricalData.  Hope this make sense.  -R-

 

by: patrickabPosted on 2009-11-05 at 10:41:23ID: 25752493

RWayneH,

1. A message box will now show if cell D7 is blank

2. The requirement to cut and paste the row corresponding to the item selected in the ListBox doesn't make sense to me. You have asked for the ListBox to display only unique items (ie no duplicates). That implies there are duplicates. So having made a selection in the ListBox how is the macro meant to know which row containing a duplicate should be copied, pasted and deleted?

Meantime - file attached with update.

Patrick

 

by: RWayneHPosted on 2009-11-05 at 10:56:49ID: 25752658

I may have miss communicated the "no duplicates" statement above.  It was meant to mean that there will be no duplicates in column D.  I have other code that checks column D prior to a record add to the table.  If it already exists in Column D, sub exits saying that you cannot add the same record twice.  This way there is not doubt what row is the target for transfer.

Sorry bout that. -R-

 

by: RWayneHPosted on 2009-11-05 at 11:04:39ID: 25752749

Actually after reviewing your edits, because of other code dependences row 7 of Sheet1 and specifically cell D7, will never be blank.  There is a placeholder record there.  If I place a value in D7, the userform should still fail, because the cell two up from the blank (in this example it would be D6, has the cell named "OneCellUpFromD7".  I was not sure how else to keep the placeholder record there. -R-

 

by: patrickabPosted on 2009-11-05 at 12:00:09ID: 25753295

RWayneH,

>It was meant to mean that there will be no duplicates in column D.

That changes the code for loading the ListBox! I will alter it accordingly. It makes it much simpler.

See attached file for hopefully all that you've asked for.

Patrick

 

by: RWayneHPosted on 2009-11-05 at 14:27:20ID: 25754788

Here is what I see in your example 03 file.  The "Show UserForm" commandbutton should fail if there is one record. (re-read the need to keep a placeholder record in the table or the use of the named cell "OneCellUpFromD7".  The other thing I see is that when I select a row that is in the middle of the bunch, it does not shift the cells up after it is cut over to Sheet2.  The last thing is the the userform stays open after a record is selected.  Can we close it?

A thought here that I would like another command button on the userform that would execute the transfer, just in case the user get click happy and mistakenly clicks before the actual one they want.  Like missing the vertical scroll etc.  Which happened to me when I was testing the file.  By accident of course...  

Thanks -R-

 

by: patrickabPosted on 2009-11-05 at 15:22:56ID: 25755216

RWayneH,

I have no idea what you mean by:

The "Show UserForm" commandbutton should fail if there is one record. (re-read the need to keep a placeholder record in the table or the use of the named cell "OneCellUpFromD7"

You never mentioned a 'placeholder' in the original question. Perhaps you'd like to raise it in a new question.

I have implemented the other items you requested - they're in the attached file.

Patrick

 

by: RWayneHPosted on 2009-11-06 at 15:22:06ID: 25763958

What part of point number 3 and 4 in the original question do you not understand?  Maybe I can clarify a bit.  

The way you have it written it displays the bottom record all the time.  That should not be there.  That is the placeholder record.  As records are added to the table it pushes that placeholder down the sheet, but as they are transfer over to Sheet2 it climbs its way back up the sheet until eventually it is the only record.

If I start a down seach at cell D7, for the first blank cell, the listbox.  If there is only one record in the table (the placeholder) would not the result of a search starting at cell D7 end at cell D8? Offset back up two cells and that will equal D6, what is the cell named: OneCellUpFromD7.  Userform should fail.

There are defined cell names in the placeholder record that are used in other subroutines that I do not want to disrupt.

Does this make better sense?  -R-

 

by: patrickabPosted on 2009-11-06 at 16:28:02ID: 25764318

RWayneH,

>The way you have it written it displays the bottom record all the time.

No idea what that means. My macro does not affect what is visible on the worksheet. It only moves data when the 'Transfer' button is pressed. Otherwise it does not change what is displayed - thus I cannot understand your comment.

>As records are added to the table it pushes that placeholder down the sheet

I don't know what you mean by a placeholder. What is a placeholder? What purpose does it serve?

>If I start a down seach at cell D7, for the first blank cell, the listbox.

Sorry but that sentence in incomplete...

>If there is only one record in the table (the placeholder) would not the result of a search starting at cell D7 end at cell D8? Offset back up two cells and that will equal D6, what is the cell named: OneCellUpFromD7.  Userform should fail.

From that I assume you mean that if cell D8 is empty then the UserForm initialisation macro should stop the initialisation process. I have now included that in the attached file.

Patrick

 

by: RWayneHPosted on 2009-11-07 at 06:34:02ID: 25766442

You are correct, the macro does not affect what is visible on the worksheet.  What I am referring to is what is displayed in the listbox.  That last record should not be in the listbox, however it will be in the worksheet.

Think of the placeholder as the record, shown on the worksheet but not in the listbox.  The purpose is for other code that is too extensive to explain...  in short there are named cells in it that are used else where in the file and in other procedures.

Your last assumptions is correct.  If D8 is empty kill the process.  However, if D8 is empty that means that D7 is not.  Row seven (the whole row is the placeholder.  I guess the only thing left now is to get whatever is in that bottom row to not show up in the listbox and we have it.

Sorry for the confusion here.  That is why I referenced offset active.cell up two, from the found blank cell.  If two cells up from that blank cell is not the cell named "OneCellUpFromD7" the userform should launch, as it returns false.

I marked  up the sample a little in the hope that it can explain further.
Thanks for hangin in there with me on this.  -R-
" D8 is empty, two up from that is a specific named cell.  "OneCellUpFromD7"  If for some reason that column D is not populated in a record.  The userform will not show, even though there are records there.

 

by: RWayneHPosted on 2009-11-07 at 06:44:15ID: 25766480

Woops! forgot to attach the file.  -R-

 

by: patrickabPosted on 2009-11-07 at 07:27:17ID: 25766582

RWayneH,

I have now excluded the last row of data from the ListBox. That took all of a few seconds. It was all the talk of placeholders, ranges named "OneCellUpFromD7" and so on that obscured the simple requirement not to include the last row of data in the ListBox and to stop the macro when row 8 was blank.

Hopefully that completes the task - see attached file.

Patrick

 

by: RWayneHPosted on 2009-11-07 at 07:50:21ID: 25766681

Thanks! we are just about there... the last minor detail..

In your file, run Userform and delete a row, (reminder the number and/or position), then run userform again.  You will notice that the value/row previously deleted is still in the listbox, even though it was transfered to Sheet2 and no longer on Sheet1.  Like it is not refreshing the listbox or something.  It is not until I quit and return a second time to the listbox that it is gone.

This may seem petty, but will confuse users.
I believe with that fixed we are golden.   Thanks. -R-

 

by: patrickabPosted on 2009-11-07 at 07:56:31ID: 25766723

RWayneH,

>Like it is not refreshing the listbox or something.

I had not 'Unloaded' the ListBox. Now done in the attached file.

Patrick

 

by: RWayneHPosted on 2009-11-07 at 09:04:02ID: 25767081

Bravo!  EXCELent.  Exactly what I was looking for.  Thanks a million and thanks for your patience during this reqest.  -R-

 

by: RWayneHPosted on 2009-11-07 at 09:05:51ID: 31650548

Sorry.. probably should have used different terms then placeholder...  It would have been less confusing.

Thanks again.  -R-

 

by: patrickabPosted on 2009-11-07 at 11:41:13ID: 25767717

RWayneH,

Thanks for the grade. Pleased we got there in the end.

Patrick

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...