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

Thread: Macro etc to select entry in drop down list

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Macro etc to select entry in drop down list

    Hi,
    Is it possible for a simple code for which when the worksheet is open the first entry in the drop down list is selected.


    have a nice day.
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,248
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Macro etc to select entry in drop down list

    Place this macro in the worksheet code module. In order for it to work, the drop down list must be generated using a formula referring to a range. Change the range (in red) to suit your needs.
    Code:
    Private Sub Worksheet_Activate()
        Dim inputRange As Range, rng As Range
        Set inputRange = Evaluate(Range("A1").Validation.Formula1)
        For Each rng In inputRange
           Range("A1") = rng
           Exit For
        Next rng
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro etc to select entry in drop down list

    Hi,
    Im getting caught out with this part,
    Code:
    Set inputRange = Evaluate(Range("G13").Validation.Formula1)
    Is the range thats mentioned the cell in which the drop down list is situated or is it the range from the other sheet from where the drop down list is populated.
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,248
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Macro etc to select entry in drop down list

    It refers to the range from the other sheet from where the drop down list is populated. As long as you used a formula referring to that range to populate the drop down list, the macro should work. Is the macro not working as you requested?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro etc to select entry in drop down list

    Mumps,
    i couldn't find this earlier so I will check it out tomorrow.
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  6. #6
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro etc to select entry in drop down list

    Hi,
    Still no joy for me as when i open the worksheet INV i see this message, Run time error 1004 Application defined or object defined error.

    So some info for you.

    Drop down list is on worksheet INV
    The cell in question is G13
    For your information i have this also in cell H13
    Code:
    =IF(ISERROR(MATCH(G13,DATABASE!A:A,0)),"",MATCH(G13,DATABASE!A:A,0))

    I have a sheet called DATABASE & the customers names are always in column A
    The range for these names are A5:A223 but that will become 224 225 226 as more customers are added.
    When i see the error message i debug & see thine line in yellow.
    Code:
    Set inputRange = Evaluate(Range("A6").Validation.Formula1)
    Many thanks
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  7. #7
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro etc to select entry in drop down list

    @mumps,

    I have just come acroiss this page that you also helped somebody with of nearly the same thing as what i require.

    https://www.mrexcel.com/forum/excel-...down-list.html

    I altered it to correspsond with my sheets etc & it works fine.
    Thought i would mention this as it might help then advise you what we could use etc.
    If the previous code in the above post will not work then maybe this code supplied below in a macro where once the workshop is open call that macro just to select the first entry as opposed to each click then going down the customers name list.

    Code:
    Private Sub SelectFirstEntry_Click()    Dim v As Variant
        With Sheets("INV").Range("G13")
            If .Value = "" Then
                .Value = Sheets("Customers").Range("A5").Value
            Else
                v = Application.Match(.Value, Sheets("DATABASE").Range("A5:A100"), 0)
                If IsNumeric(v) Then
                    .Value = Sheets("DATABASE").Range("A5:A100").Cells(v + 1, 1).Value
                Else
                    .Value = ""
                End If
            End If
        End With
    End Sub
    Thanks
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  8. #8
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro etc to select entry in drop down list

    Having said that the code in post #7 works providing a name is in cell G13 and will not work if empty
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  9. #9
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,248
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Macro etc to select entry in drop down list

    You said that the drop down list is in G13. However, this line that generates the error:
    Code:
    Set inputRange = Evaluate(Range("A6").Validation.Formula1)
    indicates that the drop down is in A6. I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  10. #10
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,248
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Macro etc to select entry in drop down list

    Put this macro in the worksheet code module for the "INV" sheet:
    Code:
    Private Sub Worksheet_Activate()
        Range("G13").ClearContents
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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