Active X ListBox

[kakiebos]

Good day all.

I have a Active X ListBox that is linked to a cell. The list range is a list of serial numbers that is used to add additional information on the spreadsheet with a vlookup function.

The listbox is working almost as expected. The number that I select in the listbox, is transferred to the cell that I want it to be. My only issue now is that the number is entered in the cell as text and not as a number.

How can I ensure that the number is always inserted in the cell in the number format and not as text. As text, my vlookup formulas is not working because it is searching for a number.

Thanks in advance.

 

[gallen]

Hi you don't post your code so can't be specific but wouldn't it be a case of ensuring the cell is formatted is numeric:
this sets the number format to zero decimal places

Sub a()
Range("A1").NumberFormat = "0"
End Sub

 

[kakiebos]

I don't have any code with any of my Active X Listboxes. Should I have? Where will I find this?
I tried to format the cell to a number format, but as soon as I make a selection in the listbox, it enters the number to the cell as text.

 

[Fluff]

Check that your list of serial numbers are actually numbers.
If I put numbers into a listbox, they come out as numbers.

 

[kakiebos]

I had it as a normal data validation list box. But because the list is over 200 long, it is difficult to search for a specific number. With a active x listbox and with a ListFillRange and LinkedCell in the properties, when I select a serial number from the dropdown list, it enter the number that I selected to the linked cell but as text. It gives me the error popup to inform me that the number is stored as Text. I want it to store the number as a number by default.
I did check the source number, it is a number. I also ensured that the linked cell is a number format, but as soon as I select the number from the drop down list, it converts the linked cell back to text.

 

[Fluff]

I cannot replicate that behaviour.
However you said

when I select a serial number from the dropdown list

There is no "Dropdown list" with a listbox. Do you mean a combobox?

 

[kakiebos]

I managed to get a work around.
I changed the linked cell to a different cell and blanked out the text to make it invisible. Then in my original cell I added a value formula with the linked cell. It appears to work for now.

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[kakiebos]

Glad you sorted it & thanks for the feedback

I would still like to know if there is a better solution for my query.

 

[Fluff]

As I cannot replicate your problem, I don't know.