Macro etc to select entry in drop down list

[ipbr21054]

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.

 

[mumps]

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.

Private Sub Worksheet_Activate()
    Dim inputRange As Range, rng As Range
    Set inputRange = Evaluate(Range("[COLOR="#FF0000"]A1[/COLOR]").Validation.Formula1)
    For Each rng In inputRange
       Range("[COLOR="#FF0000"]A1[/COLOR]") = rng
       Exit For
    Next rng
End Sub

 

[ipbr21054]

Hi,
Im getting caught out with this part,

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.

 

[mumps]

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?

 

[ipbr21054]

Mumps,
i couldn't find this earlier so I will check it out tomorrow.

 

[ipbr21054]

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

=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.

Set inputRange = Evaluate(Range("A6").Validation.Formula1)

Many thanks

 

[ipbr21054]

@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-questions/846681-macro-select-next-name-dropdown-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.

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

 

[ipbr21054]

Having said that the code in post #7 works providing a name is in cell G13 and will not work if empty

 

[mumps]

You said that the drop down list is in G13. However, this line that generates the error:

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.

 

[mumps]

Put this macro in the worksheet code module for the "INV" sheet:

Private Sub Worksheet_Activate()
    Range("G13").ClearContents
End Sub