Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automate 'Pick from List' in certain columns (2002)

    Greetings, I'd like to make it so that if a selected cell is within a certain column, the 'Pick from List' function is automatically called. Ideally, this would only occur if the selected cell is empty. Any ideas?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automate 'Pick from List' in certain columns (2002)

    This doesn't meet all requirements, but is simple:
    Data -Validation
    on the cells
    Will get list whether empty or no.

    The only way (I think) to meet the requirements is to write code to display a listbox using a worsheet-selection change event.

    Steve

  3. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Automate 'Pick from List' in certain columns (2002)

    Kel,

    As Steve mentioned, you probably want "Validation", however the following code works for me on Excel 97 and Excel 2002...

    '---------------------------------------
    Sub ShowPickList()
    If IsEmpty(ActiveCell) Then
    Application.Cursor = xlNorthwestArrow
    Application.CommandBars("Cell").Controls("Pic&k From List...").Execute
    Application.Cursor = xlDefault
    Else
    MsgBox "Select a blank cell and try again. ", vbExclamation, " Pick From List"
    End If
    End Sub
    '---------------------------------------

    Regards,

    Jim Cone
    San Francisco, CA

Posting Permissions

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