• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Scrolling a validation list via mouse Scroll Button.

sumitbansal

sumitbansal

Member
Hi,

Is there a method or procedure through we can scroll a validation list via a mouse Scroll button. The scenario is as follows:

"I am making a project for a store inventory in which there are about 4895 items. The user has to select one of the items from the list(at a time) and then as soon as he selects the item, a macro runs to add that item in the main bill and reduce the quantity of that item from the main inventory List. Now, the problem is there are 4895 items and I am just not able to use the mouse scroll option with it so that user can select the item. He has to manually use the scroll and then select the item, which the client is not happy with."


Thanks in advance,

Sumit Bansal.
 
Good day sumitbansal

You have 4895 items in a validation list!!!!FOUR THOUSAND EIGHT HUNDRED and NINETY FIVE.

I hope who ever put that lot in was able to copy and paste.....but seriously Excel is good very goodat what it does but do you not think that in this case you should be looking at Access.FOUR THOUSAND EIGHT HUNDRED and NINETY FIVE.
 
Hi Sumit ,


If you see any of the other online shopping sites such as Amazon , ebay , all of them categorise their products ( which are much more than 4895 ) , so that even to select the most out-of-the-way product , you don't need to use more than a few mouse clicks.


Designing an application where a user has to select from 4895 items using just one level of selection is not very user-friendly ; I doubt that even if the user could scroll using the mouse scroll button , he / she would be any happier.


The right way to design such an application would be to have several options :


1. Drill down through 5 or more levels of categories and sub-categories till you come to the product you are looking for


2. Allow the user to enter keywords and search


3. Allow the user to directly enter the product code


4. Have special categories such as Hot Deals , Most Popular products , products having the greatest discounts ,....


You should consider implementing any of these , or any other system which will make the product selection more user-friendly.


Narayan
 
Hi Narayan,

Thanks for the insight, will try to work around for all the four points you suggested. However, just for the knowledge sake, can validation list be made to search the keywords just like in google crome where you just type few letters and the remaining word gets auto-complete? if yes, then how?


Sumit.
 
Hi Sumit ,


I am not sure a "validation list" can do what you want it to do ; however , if user input / data entry is controlled by VBA , then it certainly can. How it can be done is not for me to say , since I have not done it using Excel VBA. I can try and let you know when I get something. However , I am sure there will be others who might have already done it ; if so , they will reply.


Narayan
 
Hi, sumitbansal!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Scrolling%20a%20validation%20list%20via%20mouse%20Scroll%20Button%20%28for%20sumitbansal%20at%20chandoo.org%29.xlsm


This doesn't exactly let you navigate thru a list using mouse scrolling, but I think it does the job just moving the mouse.


How does it work?

a) it behaves like a simple drop down list box if you keep your cursor in the left 50% side of the control

b) if you move your mouse pointer to the right 50%, then:

- within the top 40% of the height control it scrolls up

- within the middle 20% it does nothing

- within the bottom 50% it scrolls down

c) click, selects an item but doesn't move to the selected list (just for positioning)

d) double click, selects an item and moves it to the selected list


You can adjust these parameters from the VBA code.


Just advise if any issue.


Regards!
 
Hi SirJB7,

Now, this is something new to me. Thank for the worksheet. Now, the user does not even has to manually scroll the list. The user would be happy to see and use it. Let me try it on my project and lets see what the user has to say about it...


Thanks,

Sumit Bansal.
 
Hi, sumitbansal!

Glad you liked it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top