Combo Box, change bound col VBA mid typing?

[bignose2]

Hi,
Perhaps an odd & most likely impossible question.

I have a combo box with 2 column's, I just use to put the value into the field.
Just number col 0 & associated name next

Most of the time I simply start typing the number and see the matching name & select.
If I can't think of the number I would like to instead start typing the name.
It would be great for the associated numbers the shortlisted.

Ie. Change the bound column mid typing.

I think I can get it to decide if the first character pressed is a number, using keypress or key down but not sure if I can change the way it will filter the combo.

I could use another combo next to it but just think this would be quicker & neater visually.

Need most of time number & not description so can't just do it the other way round all the time.

Thanks ia.

 

[CJ_London]

at least with your alternative you can see the associated name

Otherwise you would need some code in the combo change event. Don't have time to put an example together but you would need to use the .text, selstart, sellength properties plus the isnumeric function to test if the character typed is numeric

 

[bignose2]

Thanks for that,

in the end after a few more thoughts I came up with a rough work around
Not that likely to be of interest to anyone but does work.

If (KeyAscii < 48 Or KeyAscii > 57) And (KeyAscii <> 9 Or KeyAscii <> 27) Then ' not sure working ..Not a number also not tab or enter or shift tab

If NominalList.BoundColumn = 1 Then ' don't keep changing
NominalList.BoundColumn = 2
NominalList.ColumnCount = 3
NominalList.ColumnWidths = "0cm;6.5cm;1.403cm" ' added 3rd column to source, copy of first, looks like reversed

NominalList.Dropdown

End If

Else ' Is a Number
If NominalList.BoundColumn = 2 Then
NominalList.BoundColumn = 1
NominalList.ColumnCount = 2
NominalList.ColumnWidths = "1.403cm;4cm"

NominalList.Dropdown
End If

End If


On exiting the combo, if resetting back to default just caution as changing the properties of course clears the underlying value, so temp save before.