Cell with data validation: Default to first item?

[VBA_Newbie]

Hi folks,

I have a data validated cell which takes its valid entries from a dynamic named range. A macro I run changes the dynamic named range, and hence, the allowable entries of the data validated cell.

What I'm hoping to do is have the data validated cell 'default' to the first item in the dynamic named range everytime the macro is run. Any suggestions?

Many thanks in advance!

 

[erik.van.geit]

Hi,

try this

Sub set_to_first_validationitem()
'Erik Van Geit
'050514 0108
Dim VF As String

    With Range("A1")
    VF = .Validation.Formula1
    .Value = Range(Right(VF, Len(VF) - 1))(1)
    End With

End Sub

kind regards,
Erik

 

[VBA_Newbie]

Awesome! Thank you very much...it's working great!

 

[erik.van.geit]

you're WELCOME
now it would be intresting to understand how it works ...
when I was a Newbie, and even now (knowing there are other ways to do this), I'm using this technique

Sub set_to_first_validationitem()
'Erik Van Geit
'050514 0108
Dim VF As String

    With Range("A1")
    VF = .Validation.Formula1
    MsgBox VF, 64, "VF"
    MsgBox Right(VF, Len(VF) - 1), 64, "Right(VF, Len(VF) - 1)"
    MsgBox Range(Right(VF, Len(VF) - 1))(1).Address(0, 0), 64, "Range(Right(VF, Len(VF) - 1))(1).Address(0, 0)"
    MsgBox Range(Right(VF, Len(VF) - 1))(1).Value, 64, "Range(Right(VF, Len(VF) - 1))(1).Value"
    .Value = Range(Right(VF, Len(VF) - 1))(1)
    End With

End Sub