Help for a combo box

[L.White]

I have the following code running for a combobox

Me.ComboBox2.Clear
Me.ComboBox2.ColumnCount = 2

If LCase(myCell.Value) Like LCase(myPfx) Then
Me.ComboBox2.AddItem myCell.Offset(0, 3)
Me.ComboBox2.List(Me.ComboBox2.ListCount - 1, 1) = myCell.row
End If

I don't know how to make the combo box show a third column. I want to show
the value of myCell, the offset of myCell, and the row that they come from.
Other than changing the ColumnCount=3 what line of code do I put into the IF
statement to show the value in the order listed above?

This is running in Excel2003.

 

[Bob Phillips]

Do you mean?

Me.ComboBox2.Clear
Me.ComboBox2.ColumnCount = 3

If LCase(myCell.Value) Like LCase(myPfx) Then
Me.ComboBox2.AddItem myCell.Offset(0, 3)
Me.ComboBox2.List(Me.ComboBox2.ListCount - 1, 2) = myCell.row
End If

 

[L.White]

That was what I needed. My new code looks like....

Me.ComboBox2.Clear
Me.ComboBox2.ColumnCount = 3

If LCase(myCell.Value) Like LCase(myPfx) Then
1) Me.ComboBox2.AddItem myCell.Offset(0, 3)
2) Me.ComboBox2.List(Me.ComboBox2.ListCount - 1, 1) = myCell
3) Me.ComboBox2.List(Me.ComboBox2.ListCount - 1, 2) = myCell.row
End If

Unfortunately I now have another question. When I view the contents of the
box I see the contents of column 1) completely. Column 3) is small so I can
see the full contents of it as well. Column 2) is not completely visible.
How do I control the size of 2)? When I hit the drop down I want to see the
full contents of the longest cell in the box.

LWhite

 

[Bob Phillips]

You can set the columnwidths via the Columnwidths property, but it affects
all columns, and it isn't dynamic as you seem to ask. You can also widen the
combobox.

 

[L.White]

What I have as a sample of the data is the following, each number matching
to part of the combobox.

1) SCREW FOR TOOLING BALL BOXES
2) AS30214.12.7
3) 350

I can currently read all of 1), The 7 gets cut off of 2) and I can read all
of 3). The information above is exactly what I see in my spreadsheet.
Current settings are:

column width 300pt
column count 3
bound column 1
column heads false
list rows 8
text column -1
top index -1
misc width 449.25

Are there any other settings I should list for you? Thanks for your help so
far. I really appreciate it.
L.White

 

[Bob Phillips]

Afraid I can't see the problem. I put AS30214.12.7 in A1, set myCell to A1,
put 1) in C1, and it all showed as I expected, no truncation.

You don't have any special formatting on the cell by chance?

--
HTH

Bob Phillips

What I have as a sample of the data is the following, each number matching
to part of the combobox.

1) SCREW FOR TOOLING BALL BOXES
2) AS30214.12.7
3) 350

I can currently read all of 1), The 7 gets cut off of 2) and I can read all
of 3). The information above is exactly what I see in my spreadsheet.
Current settings are:

column width 300pt
column count 3
bound column 1
column heads false
list rows 8
text column -1
top index -1
misc width 449.25

Are there any other settings I should list for you? Thanks for your help so
far. I really appreciate it.
L.White