Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Text suggestion & Type determination based on predetermined item name list...

    Hi guys,

    I was wondering:

    - Is there is a way to write a predetermined item name list, and have Excel suggest them when user starts typing the first couple of characters? [kind of like when you have a word in a cell below or above]
    - How would I go about creating a VBA that would know based on the name of an item what it's type is? Column C is item name, Column D is item type... I would like to automate this, and I have a predetermined list of items, about 90-100 possible item names.

    Thanks
    Ferenc

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    What you want is the Data->Data Validation feature. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    ..the Data Validation list will work, but can be tedious if there are a lot of entries in the validation list.
    It can mean lots of scrolling to get to your required entry.

    It is possible to have a dropdown that has the requested feature i.e. will 'scroll' the list based on the letters as you type.

    I'll fish out an example and post it here.

    zeddy
    •Fish and Wildlife Dynamic Excel Coding
    .

  4. The Following User Says Thank You to zeddy For This Useful Post:

    Ferenc Nagy (2015-07-25)

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Ferenc/RG

    ..OK, I made this example up with approx. fifteen hundred entries in a list for the data validation dropdown list. I chose Cities for my example.
    In the attached file, a dropdown will appear in column [C].
    As you type the first few letters, the dropdown will scroll to that location.

    ..the vba code is in the worksheet [Main], right-click the [Main] tab to view the code.

    zeddy
    •Nuisance Avionics Tester
    Attached Files Attached Files

  6. The Following User Says Thank You to zeddy For This Useful Post:

    Ferenc Nagy (2015-07-25)

  7. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Very nice I thought Data Validation would search as you type? Guess you can tell I don't use it much. This should be on MS's list to fix.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #6
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi Ferenc/RG

    ..OK, I made this example up with approx. fifteen hundred entries in a list for the data validation dropdown list. I chose Cities for my example.
    In the attached file, a dropdown will appear in column [C].
    As you type the first few letters, the dropdown will scroll to that location.

    ..the vba code is in the worksheet [Main], right-click the [Main] tab to view the code.

    zeddy
    •Nuisance Avionics Tester

    Thank you Zeddy,

    That is exactly what I need, but I have couple of questions about it, as almost everything in this VBA is new to me so not sure how to manipulate its behavior yet.

    - Can I change it from opening list when clicking on cell to opening list when starting to type? Is that even possible with this? Now it opens up every time I "walk" throw column C using the arrow keys, making navigation slower...

    - On the Lists sheet I entered the item type [column C] next to each Item Name [column B]... There is only like 8 item types total. Is there an easier way to make the Sub Worksheet_change enter the Type upon pressing enter after the initial item name entering? I know I could use [For] and search for the item name every time, just wondering if there is something better in this case...

    Thanks
    Ferenc
    Last edited by Ferenc Nagy; 2015-07-25 at 19:20.

  9. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Ferenc

    You can change it to only show the dropdown if the cell is empty.
    i.e. if the cell already has a value, do nothing.
    To make a change to an existing non-blank entry, you would then have to delete it first.

    Typically, when using such a dropdown, there is an 'associated data element'.
    For example, with City as the dropdown, we might want to automatically put State in the adjacent column.
    In such cases, there could be more than one applicable State for a particular City.
    In which case, if there are multiple choices for the associated State, we then provide another dropdown automatically, with only the corresponding relevant entries to choose from.
    If there is a unique value, i.e. only one particular State for a particular City, we just put this in the adjacent cell automatically.

    So, in your particular case, where you have an associated Item Type for each Item Name, we could put this in the adjacent cell automatically, each and every time the Item Type is chosen.

    I will update my example and post another demo file to show this.

    zeddy
    •Excel Contamination Crew
    Last edited by zeddy; 2015-07-26 at 05:07.

  10. #8
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi Ferenc

    You can change it to only show the dropdown if the cell is empty.
    i.e. if the cell already has a value, do nothing.
    To make a change to an existing non-blank entry, you would then have to delete it first.

    Typically, when using such a dropdown, there is an 'associated data element'.
    For example, with City as the dropdown, we might want to automatically put State in the adjacent column.
    In such cases, there could be more than one applicable State for a particular City.
    In which case, if there are multiple choices for the associated State, we then provide another dropdown automatically, with only the corresponding relevant entries to choose from.
    If there is a unique value, i.e. only one particular State for a particular City, we just put this in the adjacent cell automatically.

    So, in your particular case, where you have an associated Item Type for each Item Name, we could put this in the adjacent cell automatically, each and every time the Item Type is chosen.

    I will update my example and post another demo file to show this.

    zeddy
    •Excel Contamination Crew
    Hi Zeddy,


    Sounds great thank you. Could you set it - if possible - so that the drop down list only shows on an empty cell when user starts typing? (At the moment if a cell is empty and you want to navigate through it with the arrow keys, it will trap the cursor and won't let it out until something is chosen from the list or a different cell selected with the mouse. This slows things down significantly.)

    One more thing, when I press enter in an empty cell because I decide not to enter anything or after selecting an item from the list, the focus is gone from the sheet completely. I have to select a cell with the mouse or I won't be able to continue the work. Would this be fixed by Target.Select?

    Ferenc

    PS: I was trying to play around with it and can't seem to make the Worksheet_change work on the same sheet. Is this a condition of using this? There are several conditions checked after a name is entered so it makes the difference between viable or not.
    Last edited by Ferenc Nagy; 2015-07-26 at 12:50.

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Ferenc

    It is possible to show the dropdown list only when the User starts typing in an empty cell.
    However, this would require constant monitoring of the keys, and could affect performance.
    Also, it would mean more complications if the User wanted to change their mind after selecting an entry.

    I think it would be easier to use the right-click of the mouse to show the dropdown box.
    This would allow you to skip along cells, whether they are empty or not.

    In this attached version, a dropdown box will only appear if you right-click the mouse in column [C]
    The dropdown will scroll automatically as you type the first few characters.
    (You can also use the mouse to scroll and select as normal.)
    If you change your mind and press [Esc], it will cancel and hide the dropdown.
    The focus will return to the cell.

    If you make a selection from the dropdown (by pressing the [Enter] or [Tab] key), the routine will automatically enter data for adjacent cells.
    In this example, selecting a City will cause the County and State to be entered automatically.

    I have included a list of the vba keycode constants in the code documentation. Right-click on the worksheet tab [Main] to view the code.

    zeddy
    •Excel Entourage Event Ensembler
    .
    Attached Files Attached Files
    Last edited by zeddy; 2015-07-27 at 10:35.

  12. #10
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi Ferenc

    It is possible to show the dropdown list only when the User starts typing in an empty cell.
    However, this would require constant monitoring of the keys, and could affect performance.
    Also, it would mean more complications if the User wanted to change their mind after selecting an entry.

    I think it would be easier to use the right-click of the mouse to show the dropdown box.
    This would allow you to skip along cells, whether they are empty or not.

    In this attached version, a dropdown box will only appear if you right-click the mouse in column [C]
    The dropdown will scroll automatically as you type the first few characters.
    (You can also use the mouse to scroll and select as normal.)
    If you change your mind and press [Esc], it will cancel and hide the dropdown.
    The focus will return to the cell.

    If you make a selection from the dropdown (by pressing the [Enter] or [Tab] key), the routine will automatically enter data for adjacent cells.
    In this example, selecting a City will cause the County and State to be entered automatically.

    I have included a list of the vba keycode constants in the code documentation. Right-click on the worksheet tab [Main] to view the code.

    zeddy
    •Excel Entourage Event Ensembler
    .
    Thank you Zeddy for the help.

    I think I understand now that this is not a viable route for what I am trying to do.
    Having to use the mouse and keyboard in combination is just counter productive.
    I will go the other route and try to find the name typed in and change it based on the findings.
    I did learn from this a lot but have to admit, could not figure out what makes the County and State value show up on the Main sheet, so that was frustrating

    Ferenc

  13. #11
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Ended up using VLOOKUP for now. It does the job for the time being, and I can study excel some more to learn better ways to do it.
    Still could not crack MATCH & INDEX that you used...

    I was wondering if there was a way to make VLOOKUP check for item that contains both words typed in, rather then looking for exact match?

    Example:
    ETSU Freelancer >>> TAKUETSU Freelancer MIS 2944
    TAKU Freelancer >>> Error

    Also now I can't get if error to work in the VBA, so if I type anything that is not in the table, I keep getting an error...
    Frustrating...

    Ferenc
    Last edited by Ferenc Nagy; 2015-07-27 at 23:43.

  14. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Ferenc

    ..don't give up so easily! If you want to use the keyboard only, then have a look at this attached version.

    This example allows you to use the keyboard to display the dropdown list whenever you want, provided you are in the defined column. (You can also easily define it to work in multiple specified columns).

    When you are in column [C], to display the context-sensitive dropdown List, use my keyboard shortcut [Ctrl][L] (i.e. Control lowercase L). Excel allows you to assign keyboard shortcuts to your macros.

    You should avoid using existing combinations, e.g. [Ctrl][C] etc. for obvious reasons.
    Excel doesn't use E, J, L, M, Q, T with [Ctrl] (lowercase letters)

    Most of the Capital letters are available. To use Capitals, the keyboard shortcuts are like [Ctrl][Shift][Z] (I use that one a lot).

    Now for the technical stuff:
    Where does Excel 'store' the user-defined macro shortcut keys?
    The actual keyboard shortcut assigned to a particular macro subroutine is 'saved' as a hidden VBA line immediately after the subroutine name in the code module. If you also assign a description to the keyboard shortcut, this will be the first 'hidden VBA line'.
    (To view these 'hidden VBA' lines, export the module and then open the exported .bas file in Notepad). See this example:
    Code:
    Sub showDropdown()           
    Attribute showDropdown.VB_Description = "show my dropdown list"
    Attribute showDropdown.VB_ProcData.VB_Invoke_Func = "l\n14"
    
    r = ActiveCell.Row: If r = 1 Then Exit Sub  'ignore header row 1
    ..if you had assigned Ctrl m as the keyboard shortcut, the line would be:
    Attribute showDropdown.VB_ProcData.VB_Invoke_Func = "m\n14"

    The showDropdown is the name of the macro.
    The \n14 is the function wizard category for 'user defined'

    What happens if I open two workbooks which each have their own macro assigned to the same keyboard shortcut?
    Excel remembers the first one that was opened i.e. same shortcut keyboard assignments in subsequently opened workbooks in the same session are ignored.

    zeddy
    •Excel Fulfillment Witness
    Attached Files Attached Files
    Last edited by zeddy; 2015-07-28 at 13:14.

  15. #13
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Tomorrow flying to Hungary for a week so won't be able to continue much work on this, but I managed to do a relatively solid version of the file, so I am moving all data across now...

    Will continue when I get back.
    Ferenc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •