How to treat data in listbox as VALUES

Hello.

Please, do you know if Listbox can contain also data formated as numbers ? I read somewhere that Listbox in useform is only for text and not numbers. Thus despite I can add to listbox numbers, their format is not NUMBER but TEXT. I just want to use my numbers in listbox as filter criteria. So user select from listbox, let's say 5, and my column will be sorted only on cells containing number 5. I can use this method for TEXTs (strings), but for numbers, it's not working. Excel somehow treat with numbers in litsbox as text. Basically It's working, but only when I setup format of data in my filtering column as GENERAL. But when I want to setup number format of related cells to let's say "number with 2 decimals", then it's not working.
I tried to change data type of my listbox value, like: Val(my_userform.listbox1.value) or Cdec(my_userform.listbox1.value) or
Cint(my_userform.listbox1.value) , but no success. It's like I can't change format of data in listbox to NUMBER.

If you have some idea how can be data in listbox changed to NUMBER format, so I can work with it further (in my case to setup as criteria to filter column containing numbers), I would be very thankful to you. When I write it to textbox, it's working well, but not in Listbox.

Hello.

thank you for your advices. Here I am attaching simply file which shows my issue quite good. If you click on button "filter data", you will see listbox, which is filled by data in column A. My problem is following:
- when I setup number format of data in column A, let's say on two decimals etc, when I click on button in useform for filtering, nothing is found.
- but when format of data in column A is general, it works ! But only when I don't use any converting function in my code. Currently I tried to modify data in my listbox in following way >>

Please Login or Register  to view this content.

So if I would omit function CDbl and format and set data in column A as General, my filter wokrs, otherwise not.

My assuption is that user will change format in column A to NUMBER, 2 decimals.

Macro for adding data to Listbox

Please Login or Register  to view this content.

Macro for processing filter based on selection in listbox:

Please Login or Register  to view this content.

Replace the codes in UserForm with the following codes.

Please Login or Register  to view this content.

Thank you very much guys. I will go through your solutions and will get back here, in case I will be still in doubts. I see there some new commands and functions you used which I don't know so far.
Thanks again for your help !

Hi guys, I still don't have it :-( Jindon, I copied your codes but it's not working:-( Maybe I do something wrong.
I really don't understand it. In attachment from PCI is visible that number format is following 0.00 . When I try to change number format to 0.00, it always put instead of dot (0.00) comma (0,00). But even more strange thing is , that I placed check into my code >>> for example : listbox.list(2) = cells(2,1).value .... and it shows, that YES, it's the same. Then WHY automatic filter can't find this value ?

Basically I still think that this is not rocket sience. There is no need of special conversion of data when loading to listbox from certain cells. If data in cells are numbers, then automatically Excel retain them as number also in Listbox, right ? . Also no need to setup special format like "0.00" etc, right ? Because 18 is the same as 18.00 or 18.00000. And despite I change number format in listbox which is not the same as in cells, it shouldn't be issue, right ? As stated above, 18 = 18.00
But it seems somehow VBA must have exactly the same number format in listbox as it's in certain cells.

Please, does somebody of you have some small macro which use listbox for filtering data, especially NUMBERS ?

thanks a lot.

See..............

Hi Jindon.
I am sorry, but it doesn't work. Can't this be connected with fact, that you have Excel 2010 and me 2007 ?
This is what I see when I click on button "filter data" in your file >> 0, 1, 3, 5, 7 etc. They don't have decimals...I supposethis is result of Val function and can be easily changed to using format function.

But still, problem is, when I select some option in listbox and click on "filter" button, filter can't find this value(s). If you can work with that, it's most probably Excel version issue. Or maybe some settings, but I doubt.

Really strange to me.
It is working fine.
try this

Please Login or Register  to view this content.

thank you Jindon for your time, I appreciate it. Don't spend more time on this stupid thing.
I also don't know where is problem. Below You can see attachment. It's your file, i just added 2 screenshots into 2. tab - so you can see what I see. Whatever option i select in listbox, filter doesn't work. Maybe I have something wrong set in my excel....in settings, if you can work with it and me not, where else can be problem (excel Excel 2007-2010 version) ?

I thought that problem is that items selected in listbox and added to dynamic array x() are strings. But then I did a check, like x(0) + x(1) ...and Excel did this calculation correctly, so in array x() there were really numbers, but still when I wanted to apply selections in filter, no success. I also updated array data type from string to Variant or Double, but no success. Sometimes filter works, but ONLY when there is exactly the same number of decimal places in cell and in listbox....for me it's a sign, that item in listbox is formatted as string.

Maybe it's a time to let it be and do something else, maybe sleep :-))

No,

It if doesn't work at your side, your EXCEL should have problem.

Yes, right, probably it's because of Excel. But don't you think it's just because you have higher Excel version than me ? I tested my and your macro on different computer, where is Excel 2007 proffesional and still the same problem.
Maybe this sort of thing is in excel 2010 better covered than in 2007. In my eyes, values in listbox are still somehow taken as string and not numbers regardless how many functions I use to force Excel to convert it (Val, format, cdbl etc.) - at least in Excel 2007. And thus Excel can't find number in listbox in specified range of data.

All the Value from controls should be STRING. This is the fact.

When you filter the column with Array, criteria should match exactly the same as how it is shown in the cell.

2 will not match to 2.00 etc, so you should not apply Val function to convert it to a double.
Just run the code so you should understand what I'm saying

Please Login or Register  to view this content.

And this functionality is intrrodeuced from Excel 2007.

Hi Rudo123.
seems it works (using Jindon's code)
try

Thank you Jindon, nilem.
I really opened your files, didn't change anything and can't use that filter. So it must be connected with Excel version. I will try to instal today trial Excel (office) 2010 and see what happens. I will let you know for sure.

HALELUJA guys !! :-)
During my installation of MSO 2010 I was browsing net and trying to find some useful information. I found something saying that if somebody wants to see in listbox exactly the same as stated in some cell, he/she needs to use this command : cells(1,1).text

So instead of writing : UserForm1.ListBox1.AddItem Worksheets("data1").Cells(j, 1).Value , I wrote UserForm1.ListBox1.AddItem Worksheets("data1").Cells(j, 1).Text and then it was working. I didn't have to use any functions like Val, Format etc.
I hope there is no hidden problem in this solution. If no, then it would mean that it's enough to setup desired format in cell range and using "text" command the same format is shown in listbox.

I installed trial MSO 2010 version now, so will try to play with your macros/solutions. I really couldn't run it under MSO 2007.

Thanks a lot again for all your help, ideas. I learnt a lot ! Have a good weekend.