+ Reply to Thread
Results 1 to 5 of 5

Populating ComboBox, how to get Column from Header?

  1. #1
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Ladson SC
    MS-Off Ver
    Office365
    Posts
    1,163

    Populating ComboBox, how to get Column from Header?

    I hope my title is helpful, I'm not sure how to state the title.

    I have a ComboBox, I am using Range A1:E1 for a list.
    When I select from the list, I need to know what Column the text was from.
    I don't know how to store or retrieve that when I create my list.

    This is the code (Found from a users post) to populate the ComboBox.
    Pretend A1:E1 are as follows:
    01 02 03 04 05

    PHP Code: 
    Private Sub UserForm_Initialize()
    Dim objCell As Range
    Dim colNoDuplicates 
    As New Collection
    Dim vItem 
    As Variant

    ComboBox1
    .Clear

    On Error Resume Next
    For Each objCell In Range("A1:E1")
       
    colNoDuplicates.Add objCell.ValueCStr(objCell.Value)
    Next objCell
    For Each vItem In colNoDuplicates
    MsgBox vItem
    .cell.Address 'for testing
       ComboBox1.AddItem vItem
    Next vItem
    ComboBox1.ListIndex = 0

    End Sub 
    Regards

    Rick
    Win10, Office 365

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: Populating ComboBox, how to get Column from Header?

    you can use a two-column comboox with only the first column visible and in the second column you list the column number or letter, home it makes sense
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: Populating ComboBox, how to get Column from Header?

    I did not use the noduplicates in your sample code snippet but just the simple range and a lable to show the results and the idea
    Quick and dirty
    It's bedtime here, so I'm off to bed.
    Happy coding
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,223

    Re: Populating ComboBox, how to get Column from Header?

    Assuming that there are no duplicates in the range A1:E1, the code can be significantly simplified. Only one column in ComboBox1 will be needed:
    Please Login or Register  to view this content.
    Artik

  5. #5
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Ladson SC
    MS-Off Ver
    Office365
    Posts
    1,163

    Re: Populating ComboBox, how to get Column from Header?

    Thank you for all the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Auto-populating header row and column of table
    By DPashley in forum Excel General
    Replies: 3
    Last Post: 04-30-2018, 12:24 PM
  2. [SOLVED] populating combobox with certain data from a column
    By bqheng in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-26-2017, 08:58 AM
  3. Populating a cell with a column header name
    By Niclal in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-27-2015, 02:53 AM
  4. Populating a cell with a column header
    By Patty McJorst in forum Excel General
    Replies: 8
    Last Post: 05-14-2013, 11:53 AM
  5. Replies: 2
    Last Post: 03-11-2013, 04:29 AM
  6. [SOLVED] Populating a Multi column Combobox in a userform
    By anandvh in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-14-2012, 04:11 PM
  7. populating worksheet column from combobox
    By chris_norton in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-05-2009, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1