Get Find and mouse wheel scroll working in list box

[taralmehta15]

Hi Friends,
I have a sheet in which I select products from listbox and extract it to the columns.(Sample sheet attached.) Having a few difficulties as follows:

1- listbox doesnt work if I save the file with that particular sheet active. For eg: my listbox is in the sheet named 'order'. If i save the file with cell in that sheet, next time when I open the file ('order' sheet will be displayed as dat was the 1 while savin the file), listbox wont work. I have to select other sheet and then go back to the 'order' sheet to get listbox working.

2- I have around 4500 products and it becomes very difficult to find a particular product from it. (Although I have it sorted alphabetically but still, its huge list). I woud prefer if i can find products by typin a few words (filtering as i type).

3- I am not able to use mouse wheel to scroll through the list box.

I would really appreciate if ne1 can help me in solving this (or suggest a workaround to make it easier). I dunno much about vba. Have learnt a bit through dis forum and google only. Hence, Please mention a bit in detail if I have to modify the vba or nethin such..
Thanx a lot..

[LordLoki]

Hi tara,

i will check on it later if no one else does something.
Can you tell me what Reg No. and Reg Date are Doing in this thing?

If i see it correctly you want to create an Order List where the user can Choose the Products from the Listbox and then save it to a specified location
is that correct or does it need to do something else that i missed?

Greets Loki

[taralmehta15]

Hi,
Reg No. is the no. by vich i save the order. Its just like the order number.(But the order number is given by the companies who give me order while reg no. is the no. by vich my company saves the order.)

I have a chemical manufacturin company. the orders vich I get are mostly scanned copies of documents or sometimes even handwritten copies sent through whatsapp/email etc..Many orders have more than 100 products listed. Typin all of them is much time consuming. So instead of typin, I created a listbox of all the products and now I just have to select the products and extract it to 'A" column of the sheet and enter remaining data.. (Then I use barcode to pack the ordered products and create bill using a macro to get name address and other general info from the 'order' sheet and the packed products from barcode sheet. But dis part is workin fine so I havent attached those sheets here.) Its the listbox part I am havin trouble with. I can attach entire file if needed.

Btw, my name is Taral

[LordLoki]

Hi Taral,

Sorry for the wrong name its not the first time that happens to me :D
Could you upload a final Order Sheet with removed customernames etc so that i can see how the Final result should Look?
Then i will make you a form that should help you speed up the whole process

Greets
Loki

[taralmehta15]

Sure.. I just attached it here with steps explained:

1- Select products (from listbox)

2-click on "order" command button (to extract data to cell) In this attached file, Product Number and product Name are present in the list and both are extracted Initially in columns A and B. Currently I am adding third column "unit/Packing Size" in the list box along with Product No. and Product Name, so that all 3 columns are extracted instead of 2.

3- ctrl+shift+O generates serial no. (Coz i dint knew how to extract data from list box directly to column B and C. So added column for serial no after extractin data)

4 & 5-Entering Name and other info

6- ctrl+shift+s saves the file with registration No. In the specific folder.

I use the template file to create new order everytime.As the product list is very big, it becomes difficult to scroll and find product. Hence if by typin few words if i can shorten the list(filterin as i type) It can still reduce time to find the products.

Thanx a lot for such prompt replies.

[LordLoki]

hi Taral,

Attached you find my version of your Code. I went from Listbox in a workbook to a Userform with a Listbox. and some fields for Quantity etc.
You Can filter the Listbox by typing in a Productname or number in the searchfield and press enter or the search button.
You can also just type in a part of the product name he will list all products that have the search string in their name.
When you hit "Add to list" all selected items are added to the list and the formula for total amount is updated
If you have selected multiple items all will get the same amount unit rate qty etc. but you can leave it blank and edit manually
The part on the right sight is for the Order Number name etc. You can enter everything and when you hiz finish order the file will be saved and you will be asked if you want to clear the order form
if you click yes the order form will be cleared and the form closes.
If you click no the form stays open

If you try to save a file with a name that already exists it will tell you so that you can change the order number and hit finish order again.

Hope you like it

Greets
Loki

[taralmehta15]

hi,
Thanx a lot.. Dat definitely looks much better than mine was.. A few things though:

1-search didnt work. I tried to find the word 'zinc' and 'acetone' but the list stayed as it is,didnt give search results. Mouse scroll also doesnt work (vich wont matter if search works)

2-How to add 3rd column to the list box? As unit for most products are different I made a table with Packing size/unit as 3rd column in the data.(I had planned on using index & match function in the file i had made but urs is better and if Packing size/unit can be included in list directly,it would be easier) I tried to do it by understanding the vba from ur file but made sum mistake n hence wasnt successful.

3-It would be preferable if the order form just closes on clicking "finish order" instead of saving it, because most products have different rates and order quantity is also different. So in almost all the orders, after selecting products, rate and quantity will be entered manually. Maybe V can have another command button so after entering rates and qty, v can jus click it to save the file using Reg Name in the specific folder.

[LordLoki]

try pressing enter for the search i forgot to paste the code to the button :D
3rd column is very easy to add will make you one with 3 columns

dont understand your third point
You can just click add product without adding quantity etc then it will be blank.
When you hit finish order it just saves the order sheet with the data
Mouse scroll can be done but is very nasty thats why i added the search

[LordLoki]

And here you go 3rd column and working Search Button, Enter does still work. choose what you prefer

And for your 3rd question. I made the userform non modal so you can work on the Sheet while the Form is open.
You could also add everything then close the form and make your changes ( just leave Qty status etc blank)
When you are done you open the form again fill in the Data on the right sight and hit Finish order then.

Works without any problem

And for the code understanding

[taralmehta15]

Oops.. I tried pressin search more than 10 times but dint think of usin enter at that time in the previous file..I almost did it right with third column .. Searching was case sensitive but i ve modified it to be insensitive so dat works fine. Just 1 thing now:

I realised that when I search a product and select its checkbox, and then search other product, the checkbox from the previous products is removed and hence entire list is not extracted,only the last searched and selected product is extracted. Its probably pressing the enter/search button that erases all the previously selected checkboxes along with displaying the search results. (If i dont use search, I can select multiple products and extract them without a problem).

Maybe a small change can fix dat..

[LordLoki]

Hi taral,

The behaviour with the search is cause I clear the listbox. I could change it so that it instead deletes all that is not selected. Should not be difficult will. When I have time I will look into it .

Greets Loki

Gesendet von meinem HTC One mit Tapatalk

[LordLoki]

was easier then i thought :D
Finished it on the way to work

1. changed Search insensitive (so you don' have to do it again
2. Moved the Search Code to own function (so you dont have to change it in 2 places enter and button)
3. Instead of clearing the list when search is performed it now deletes all unchecked entrys then loads the search

Greets
Loki

[taralmehta15]

Dat is perfect..Exactly how I want and way much better than how I was plannin on doing it..

Dis forum is probably th fastest forum I ve ever seen, thanx to u guys..Without ur help it would ve taken days to get the result. Here, u solved it in less than 24 hrs.. Simply awesome..I got to learn new things and I hope I can contribute my part (not in vba I guess :D, but a lil in functions and general) to guide other members..

Reps added.. Thanx a lot..Ve a great day..!!

[LordLoki]

Hi Taral,

Always happy to help
Especially when someone already tried something on his own and is willing to learn
Thanks for the Reps and have a nice Day

Greets
Loki

[taralmehta15]

Hi..

I found a vba to highlight the listbox row on mouse over using x & Y coordinates. Tried to use it in this file. I replaced listbox name and sheet name matchin the file.. But it gives " Runtime error 11. Division by zero" or Runtime error 438: Object doesnt support this method" ( With 2 codes that I tried)

Kindly help me to make it work with my current file..(attaching the current file: trial 4 and the file from which i got the code:Scroll Listbox using highlight)

[LordLoki]

Hi Taral,

Attached the Working version.
Note you don't get the blue highlighting cause you are using a multi select listbox. there is just a thin frame around the item.
Also the accuracy is not very good if you move the mouse very slow its ok but when you go faster its not so nice
Personally i think its not very useful :D

Greets
Loki

[LordLoki]

find attached a version where you can use the mousewheel to scroll the list then u can use spacebar to select the highlighted entry

Greets
Loki

[taralmehta15]

Actually I thought of adding it just so that it can be easy to c an entire row clearly bcoz I ve many products with different packing size Eg. Acetone would be in 250ml,500ml,1lit etc and all would be in sequence hence thought if i can get to know vich row I am selecting (before selecting it), It would be less error prone..

Both of them works well but ya, as u said, it isnt very helpful and also it probably looks Clear without using them. Also, I lll get to know the selected product once i select it so its not a problem..

Thanx a lot..

Have a great day..