Results 1 to 5 of 5

ComboBox or List Box - BoundColumn and TextColumn

This is a discussion on ComboBox or List Box - BoundColumn and TextColumn within the Excel Questions forums, part of the Question Forums category; Hello I'm trying to set up a combobox on a userform. I want the combobox to show one set of ...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Location
    Shanghai, China
    Posts
    144

    Default ComboBox or List Box - BoundColumn and TextColumn

    Hello

    I'm trying to set up a combobox on a userform. I want the combobox to show one set of data but have a separate value associated with each selection, e.g. January =1, February = 2, etc.
    "Aha!", I thought, "BoundColumn and TextColumn is exactly what I need." However, I can't get this to work! Despite setting BoundColumn and TextColumn, I always see _all_ the columns in the control. Even the Excel helpfile example shows all the columns. What am I doing wrong?

    Another, related problem is that the online Excel help implies that the column wdiths can be set individually. But the columnwidths property of a combobox or listbox only seems to be able to be set for ALL columns.

    I''m using Excel 97 under Windows NT.
    Does anyone have any suggestions?


    Regards
    HedgePig

    P.S. Am posting the helpfile example too.


    From the Excel 97 online help.
    ==================================================
    The following example uses the TextColumn property to identify the column of data in a ListBox that supplies data for its Text property. This example sets the third column of the ListBox as the text column. As you select an entry from the ListBox, the value from the TextColumn will be displayed in the Label.
    This example also demonstrates how to load a multicolumn ListBox using the AddItem method and the List property.

    To use this example, copy this sample code to the Declarations portion of a form. Make sure that the form contains:

    ˇ A ListBox named ListBox1.
    ˇ A TextBox named TextBox1.

    Private Sub UserForm_Initialize()
    ListBox1.ColumnCount = 3

    ListBox1.AddItem "Row 1, Col 1"
    ListBox1.List(0, 1) = "Row 1, Col 2"
    ListBox1.List(0, 2) = "Row 1, Col 3"

    ListBox1.AddItem "Row 2, Col 1"
    ListBox1.List(1, 1) = "Row 2, Col 2"
    ListBox1.List(1, 2) = "Row 2, Col 3"

    ListBox1.AddItem "Row 3, Col 1"
    ListBox1.List(2, 1) = "Row 3, Col 2"
    ListBox1.List(2, 2) = "Row 3, Col 3"

    ListBox1.TextColumn = 3
    End Sub

    Private Sub ListBox1_Change()
    TextBox1.Text = ListBox1.Text

    End Sub

    Copyright(c) 1996 Microsoft Corporation.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default Re: ComboBox or List Box - BoundColumn and TextColumn

    It's easy... try this. Create a new userform with one combobox and one listbox.

    Now put this code in there and run the form:

    Option Explicit

    Private Sub ComboBox1_Change()
    ****If ComboBox1.ListIndex >= 0 Then
    ********MsgBox "Value: " & ComboBox1.Value & vbNewLine & "Text: " & ComboBox1.Text
    ****End If
    End Sub

    Private Sub ListBox1_Change()
    ****If ListBox1.ListIndex >= 0 Then
    ********MsgBox "Value: " & ListBox1.Value & vbNewLine & "Text: " & ListBox1.Text
    ****End If
    End Sub

    Private Sub UserForm_Initialize()
    ****Dim i As Long
    ****
    ****ComboBox1.ColumnCount = 2
    ****ComboBox1.ColumnWidths = "; 0"**'Make the second column invisible
    ****ComboBox1.BoundColumn = 2****** 'The .Value is based on column 2
    ****ComboBox1.TextColumn = 1********'The .Text is based on column 1
    ****
    ****ListBox1.ColumnCount = 2
    ****ListBox1.ColumnWidths = "0; 100"****'Hide the first, force the second to 100 pixels
    ****ListBox1.BoundColumn = 1************'.Value is based on Column 1
    ****
    ****For i = 1 To 12
    ********ComboBox1.AddItem Format$(DateSerial(2004, i, 1), "mmmm")** 'Month name
    ********ComboBox1.List(ComboBox1.ListCount - 1, 1) = i**************'Second column
    ********
    ********ListBox1.AddItem Format$(DateSerial(2004, i, 1), "mmmm")** 'Month name
    ********ListBox1.List(ListBox1.ListCount - 1, 1) = i**************'Second column
    ****Next i
    End Sub
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Jun 2002
    Location
    Shanghai, China
    Posts
    144

    Default Re: ComboBox or List Box - BoundColumn and TextColumn

    Juan

    Thanks for your reply - that's _exactly_ what I want.
    And much clearerer than the Excel helpfile!

    HedgePIg

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default Re: ComboBox or List Box - BoundColumn and TextColumn

    Cool... but still, if all you want is display a list of months, don't bother doing the second column... just use

    Code:
    Dim TheMonth As Long
    
    TheMonth = ComboBox1.ListIndex + 1
    and that's it ! TheMonth will give you the month number of the item that they selected in the combobox...
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Location
    Shanghai, China
    Posts
    144

    Default Re: ComboBox or List Box - BoundColumn and TextColumn

    Juan

    Actually I'm not wanting to display months - I just thought that would be an easier example to illustrate my problem! But once again thanks for your help - I really was stuck until your reply came along.

    Regards
    HedgePig

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com