VBA ValidateList

[mar050703]

Hello,

I am trying to write some code that will allow me from a userform to place a validate list option in cell, but default to the first item in that list. I have the following code which seems to work to enable the cell to become a validatelist.

Private Sub CommandButton1_Click() 
    Dim rCl As Range 
     
    With Sheets("Sheet1") 
        Set rCl = .Cells(.Rows.Count, 1).End(xlUp).Offset(1) 
    End With 
    rCl.Value = txtamt 
     
    With rCl.Offset(0, 3).Validation 
        .Add xlValidateList, xlValidAlertStop, xlBetween, "=Name" 
        .InCellDropdown = True

    End With

and I am using the following code to automatically select the 1st item, but it fails with an invalid qualifier error.

 rCl.Value = Replace(ActiveCell.Validation.Formula1, "=", "").Cells(1, 1).Value

Thank you for any assistance

Mar050703

 

[SkipVought]

BTW, regarding the DATES, did any of that makes sense?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

Skip

The dates did make some sense, but I remember ages ago when writing code, I had huge problems formatting dates, and I was advised to do it the way I did. I like to have my dates showing as dd-mmm-yy.

I may now be able to get away from the short date with the structured table.

Thanks