Scrollable Lists

    Hi everyone


    Sorry to be here asking another question. I have set up lists using data validation so that i can pick from a drop down, is there any way to make these lists scrollable (using the mouse wheel) or so you can type a letter to find something alphabetically rather than having to use the arrow on the list to scroll all the way up and down?



    Thanks very much

    Morning Carim!


    As normal i think I've not explained myself very well! I'm looking to be able to search the drop down either by clicking to open it then type a letter to jump to that section of the choices in the dropdown or to be able to scroll up and down the list using the mousewheel (but without hovering over the up and down bar).


    These things might not even be possible?



    Thanks

    Sorry Carim, I'm an idiot! I see now that the scroll thing is working - is it possible to tell me how you've done it as i like to understand. Also is the typing the letter thing possible?


    Thanks very much

    Sorry I'm being a massive pain. So I've seen there is code in the worksheet. I already have a little bit of code in the worksheet that I'm working on:


    Private Sub Workbook_Open()
    MsgBox "Form must be fully completed"
    End Sub


    how do i make sure this code still runs as well, do I just paste one after the other?


    Also, maybe i need to make a new post but the above code makes a box display when opening the file, really i want it to show when the form is closed down instead but I couldnt figure out how to do that.


    Thanks again

    Hello Wendy,


    Many many different questions ...


    Let's try to clarify :


    1. Private Sub Workbook_Open() has to be stored in the module named ThisWorkbook


    2. Private Sub Worksheet_SelectionChange(ByVal Target As Range) has to be stored in the sheet module : Sheet1 for example ...


    3. HookValidationList is to be stored in a standard module ...such as Module1 for example


    Hope this clarifies

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hmmm pasted those all in to the actual spreadsheet I'm working on but it doesn't seem to work. Is there anything I need to change if it's going into a different spreadsheet? I can't see any specific cell or sheet references or anything. Sorry for all the confusion, I'm really terrible at explaining things. I'm guessing there is no easy way to add the alphabet thing in, I don't want to do anything too complicated as I want to be able to understand it.

    This is so bizarre, it's now working suddenly. This is what happened with the test, i thought it wasn't working then a few minutes later it did. How very odd. Should I make a new post about the open sub question and the alphabet thing? Thanks very much

    Two remarks :


    1. In my Message # 6 point 1 ... you have the explanation about where to store the Open() code


    2. What you are calling the ' the Alphabet ' is in fact a feature named ' Autocomplete ' ...


    It is indeed feasible ... but rather complex ... and, if I may ..., a bit too early for you to tackle ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Agreed - don't tell me if it's too complicated!!!!
    Sorry I meant, is there a way to get the message on the open sub to actually show upon closing the spreadsheet instead of when opening it.


    Do you have a delay on the scrollable list as well? It's very strange, I have to leave the spreadsheet open for about 2 minutes and then it starts working, perhaps my pc just can't cope with it!!!!!


    Thanks for all your help Carim

    Regarding the message to appear as you are Closing the workbook ...( to be stored in ThisWorkbook )


    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
  MsgBox "Form must be fully completed"
End Sub


    As far as what triggers the mouse wheel hook ... you need to select a cell ... say cell A1 .. and then move to cell A2 ... :wink:


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi Carim


    When you say select a cell, do you mean by clicking in it with the mouse? I wonder if the other protections I have on the sheet is causing it to be difficult.


    Closing code works a treat - thank you so much!

    Hi Wendy,


    Thanks for your Thanks ...AND for the Like ...:smile:


    Regarding your ' highly sophisticated other protections ' ... ( difficult to guess what you referring to ...)


    in my opinion, they should not interfere with a simple cell selection change ...


    But since you are sitting in front of your worksheet ... it should not take too long to test if an obstacle arises ... ( or not ) ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Clicking into cells doesn't seem to make it start working any faster...lol it's not even remotely sophisticated!!! Just password protected and ability to format etc removed!

    Quote from WendleB

    Clicking into cells doesn't seem to make it start working any faster...lol it's not even remotely sophisticated!!! Just password protected and ability to format etc removed!


    Fair enough ...


    But if you are selecting cell A1 ... and then selecting A2 ...


    Do you get a ' scrollable data validation list ' with your mouse wheel ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hello,


    Should you still face unexpected obstacles ... do not hesitate to attach your file ...


    If your data is confidential ... anonymize your sheet ..:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Interesting, that seems to be working this morning but bizarrely my number lock goes off every time i use the sheet now. I'm not sure whether that's just coincidence though! Thanks so much for your help Carim :)

    Hopefully the macro will function beyond ....


    Wednesday Mornings ... :facepull:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    With your totally unbridled creativity ...


    Who can guess what the next .. surprise ...will be ...


    :gift:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login