Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Location
    near Blairgowrie, Perthshire, Scotland
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    pick list and VBA (XP)

    In the next empty cell in a list Shift+F10,K brings up the pick list. Is it possible to double click in that cell and instantly bring up the pick list using, say, BeforeDoubleClick - as the key combination is awkward to say the least. I don't want to disable the context menu.

    Thanks

    Rex Last

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: pick list and VBA (XP)

    Alt + down arrow also brings up the pick list.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: pick list and VBA (XP)

    Is the Autocomplete for cell values enabled in Excel under the Tools Edit command. If this is on, then all you have to do is type the first couple of Characters and excel will complete the selection if you press enter?
    I never use that Pick a list option!
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: pick list and VBA (XP)

    You could create an event procedure for the BeforeDoubleClick event of the worksheet, but that means the pick list will come up whenever you double click a cell, even if it is empty. The pick list is not exposed in VBA, as far as I know, so you cannot inspect it to see if it is empty.

    The code should go in the worksheet module (right-click the sheet tab and select View Code from the popup menu):

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Application.CommandBars.FindControl(ID:=1966).Exec ute
    End Sub

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Location
    near Blairgowrie, Perthshire, Scotland
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pick list and VBA (XP)

    Thanks for those speedy responses! The point about the pick list is that if a series of words begin with similar letters cots, costs, coats, coots and so on, the AutoComplete option only kicks in at the first unique letter.

    Rex
    PS Alt plus downarrow was new to me!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: pick list and VBA (XP)

    Alt+down arrow is built-in, so it uses the native feature: if you type one or two characters, then press Alt+down arrow, the list will be opened at the first matching item.

    I don't think it's possible to mimick this behavior in VBA except by rewriting the feature from scratch yourself - not something I fancy.

Posting Permissions

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