Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Select Nth Item From Validation List Macro

  1. #1
    Join Date
    21st August 2007
    Posts
    7

    Select Nth Item From Validation List Macro

    Hi, I have a normal data validation box on excel, but I want to be able to navigate my way through it using a macro. I can select & opent the drop down list with the following code:
    VB:
    SendKeys "%{DOWN}" 
    
    
    but then an action like
    VB:
    SendKeys "{UP}" 
    
    
    Does not navigate itself up the menu as a normal keystoke would. Any ideas on how to solve this? Basically, I want to select a cell, open the drop down menu, select the first choice in it (so thinking SendKeys "{HOME}{ENTER}" ) all using a macro.

    Thank you
    Last edited by Jack in the UK; August 22nd, 2007 at 00:45.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898
    Welcome to the forum. However please read the rules and use code tags for VBA. I have added them for you this time, but normally the post would be deleted.
    .

  3. #3
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,232

    Re: Macros To Navigate Through Normal Dropdown Data Validation Lists

    The following macro, when placed in the sheet module of the sheet with the validations, seems to work in Excel 2003. Perhaps you can adapt it to your situation.
    VB:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
        Dim s As String, v As Variant 
        On Error Goto Done 
        s = Target.Cells(1).Validation.Formula1 
        If Left(s, 1) = "=" Then 
            Target.Cells(1) = Range(Mid(s, 2)).Cells(1).Value 
        Else 
            v = Split(s, ",") 
            Target.Cells(1) = Trim(v(LBound(v))) 
        End If 
    Done: 
        On Error Goto 0 
    End Sub 
    
    
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

  4. #4
    Join Date
    21st August 2007
    Posts
    7

    Re: Macros To Navigate Through Normal Dropdown Data Validation Lists

    Thank you,
    but I can't seem to run this, and I don't think I have a validation worksheet, because i did the data validation manually through excel without a macro....

    any other suggestions?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,232

    Re: Macros To Navigate Through Normal Dropdown Data Validation Lists

    It shouldn't matter how the validation was applied to a cell. As long as the validation type is a list, the macro, when placed in the sheet module of a sheet, should automatically place the first entry of the validation list for a cell whenever the cell is selected by a user. You don't run the macro yourself; it fires when a cell is selected.
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,707

    Re: Macros To Navigate Through Normal Dropdown Data Validation Lists

    Why not simply place the 1st Item from your list into the Validated cell?

  7. #7
    Join Date
    21st August 2007
    Posts
    7

    Re: Select Nth Item From Validation List Macro

    ...well I want to later use it to navigate through the menu/lsit & loop it so that the item chosen has a specific criteria (the date>todays date) I have a whole list of these drop down menus that in turn i would like to loop through all :-/

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,707

    Re: Select Nth Item From Validation List Macro

    Loop the the List on the Worksheet and compare each cell to the Validated cell. Or use the Worksheet Change Event to it check automatically each time a choice is made.

  9. #9
    Join Date
    21st August 2007
    Posts
    7

    Re: Select Nth Item From Validation List Macro

    Ok I will try this, but there are about a hundred lists, will that not just create a massive macro, and make the document too large?

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,707

    Re: Select Nth Item From Validation List Macro

    You can make the search a LOT more efficient with use of the Find Method.

    But that aside, why do you think looping through the validation list (not sure it can even be done) will be better than looping the list source?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: April 25th, 2009, 21:30
  2. Data Validation List Item Limit
    By Claudia Paalvast in forum EXCEL HELP
    Replies: 2
    Last Post: March 7th, 2008, 10:57
  3. Replies: 7
    Last Post: April 8th, 2007, 07:33
  4. Validation List: Wide as Widest List Item
    By zura04 in forum EXCEL HELP
    Replies: 19
    Last Post: October 31st, 2006, 07:44
  5. IE automation -- select item from list
    By leaftye in forum EXCEL HELP
    Replies: 3
    Last Post: December 20th, 2005, 21:16

Bookmarks

Posting Permissions

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