Announcement

Collapse
No announcement yet.

Select Nth Item From Validation List Macro

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    Code:
    SendKeys "%{DOWN}"
    but then an action like
    Code:
    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, 00:45.

  • #2
    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.
    .

    Comment


    • #3
      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.
      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.

      Comment


      • #4
        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?

        Comment


        • #5
          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.

          Comment


          • #6
            Re: Macros To Navigate Through Normal Dropdown Data Validation Lists

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

            Comment


            • #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 :-/

              Comment


              • #8
                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.

                Comment


                • #9
                  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?

                  Comment


                  • #10
                    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?

                    Comment


                    • #11
                      Re: Select Nth Item From Validation List Macro

                      I don't really know why I thought that. I guess because I'm not too familiar with the find function, & couldn't work it out, and having the list there in the drop down menu seemed easier to do if only the SendKeys function worked properly on it....

                      Comment


                      • #12


                        Re: Select Nth Item From Validation List Macro

                        You've received several suggestions now, but realize that we don't know the nature of your data, its layout, or what you are trying to accomplish. I've shown you an example of how you can access the validation list for a cell using code that uses the properities of the validation rather than trying to use send keys. Dave has suggested other ways that may allow you to achieve the result you need. I doubt we can provide more help at this point without furtjher information.

                        Perhaps you can rephrase your question by asking how to accomplish the end you seek, rather than assuming a particular means (e.g., accessing the validation list of a cell). If you do so, please start a new thread with an appropriate title.
                        Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

                        Comment

                        Working...
                        X