ComboBox or List Box - BoundColumn and TextColumn

HedgePig

Board Regular
Joined
Jun 27, 2002
Messages
146
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboBox1_Change()
    <SPAN style="color:#00007F">If</SPAN> ComboBox1.ListIndex >= 0 <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Value: " & ComboBox1.Value & vbNewLine & "Text: " & ComboBox1.Text
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ListBox1_Change()
    <SPAN style="color:#00007F">If</SPAN> ListBox1.ListIndex >= 0 <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Value: " & ListBox1.Value & vbNewLine & "Text: " & ListBox1.Text
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    ComboBox1.ColumnCount = 2
    ComboBox1.ColumnWidths = "; 0"  <SPAN style="color:#007F00">'Make the second column invisible</SPAN>
    ComboBox1.BoundColumn = 2       <SPAN style="color:#007F00">'The .Value is based on column 2</SPAN>
    ComboBox1.TextColumn = 1        <SPAN style="color:#007F00">'The .Text is based on column 1</SPAN>
    
    ListBox1.ColumnCount = 2
    ListBox1.ColumnWidths = "0; 100"    <SPAN style="color:#007F00">'Hide the first, force the second to 100 pixels</SPAN>
    ListBox1.BoundColumn = 1            <SPAN style="color:#007F00">'.Value is based on Column 1</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 12
        ComboBox1.AddItem Format$(DateSerial(2004, i, 1), "mmmm")   <SPAN style="color:#007F00">'Month name</SPAN>
        ComboBox1.List(ComboBox1.ListCount - 1, 1) = i              <SPAN style="color:#007F00">'Second column</SPAN>
        
        ListBox1.AddItem Format$(DateSerial(2004, i, 1), "mmmm")   <SPAN style="color:#007F00">'Month name</SPAN>
        ListBox1.List(ListBox1.ListCount - 1, 1) = i              <SPAN style="color:#007F00">'Second column</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Juan

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

HedgePIg
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top