User Form ComboBox: Number to VLOOKUP not working

[mark2323]

I have a worksheet ("SUBSTITUTIONS") that contains a ComboBox, with the following property settings:

ListFillRange: SUBSTITUTIONS!A14:E25

LinkedCell: SUBSTITUTIONS!A6

BoundColumn: 1 - This column is a number - and source data is formatted as such

The next 25 cells (A7:A26) in Row A are filled with VLOOKUP formulas that read cell A6, and then properly run the VLOOKUP to pull data from range A14:E25. Great. Well, I decided to declutter the page a bit and to create a "pop up" UserForm containing a ComboBox with the following property settings:

RowSource: SUBSTITUTIONS!A14:E25

ControlSource: SUBSTITUTIONS!A6

BoundColumn: 1 - This column is a number - and source data is formatted as such

After the selection is made in the UserForm ComboBox and I return to the Worksheet, the VLOOKUP formulas in Row A does NOT update properly. This despite pulling from the same source data. I have also tried making sure the A6 cell is formatted as a Number. Even after manually making that change, the VLOOKUP's still do not update properly. Any idea on what the disconnect is between the sheet ComboBox working, and the UserForm ComboBox not?

 

[Anthony47]

A combobox returns a Text, even in case the source data is number.
So if you need to VLookup the output against a list of Numbers you have to convert it, such as

VLOOKUP(VALUE(SUBSTITUTIONS!A6),etc etc)