Data validation automatically set to first item in list

[dirtychinchilla]

Morning sirs,

I've been having a good look around for this, but am struggling to find a solution that works.

I have various data validation lists, and would like them automatically to be set to their first value when they are set up (uses a macro). Does anyone have any idea how this might be done?

Thanks in advance,

dirtychinchilla

 

[dirtychinchilla]

It's the code that you gave me.

 

[Andrew Poulsom]

What error did you get? You don't need to select anything, but it does use the active sheet.

 

[dirtychinchilla]

If I run the test macro, I receive the "Method 'Range' of object '_Global' failed" error.

 

[Andrew Poulsom]

What's Validation.Formula1 when you get that error?

 

[dirtychinchilla]

Hi Andrew,

I must apologise for my lack of response; I was off for a couple of days and have been focused on something else since Monday.

Validation.Formula1 is "=INDIRECT(UnitTypeTran)" which is the translator for one of the data validation boxes.

 

[Andrew Poulsom]

My code expects an explicit reference to the name, not a formula with INDIRECT.

 

[dirtychinchilla]

Oh OK, I really need the indirect in there, so is it possible to work with it?

 

[Andrew Poulsom]

What does UnitTypeTran refer to?

 

[dirtychinchilla]

Basically, I use data validation ranges to select a product. But the products are called things like AM 100, AM 300 etc. and the spaces are deemed necessary. But to make the data validation change according to your selection of AM 100 or whatever, the product you click on is then translated (hence the "Tran") ie Am 100 gives a range of AM_100. This is done in the UnitTypeTran range, which just uses an if statement.

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Cell As Range
    For Each Cell In ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation).Cells
        With Cell
            If .Validation.Type = xlValidateList Then
                If InStr(.Validation.Formula1, "=INDIRECT") > 0 Then
                    .Value = Range(Range(Replace(Replace(.Validation.Formula1, "=INDIRECT(", ""), ")", "")).Value).Cells(1, 1).Value
                Else
                    .Value = Range(Replace(.Validation.Formula1, "=", "")).Cells(1, 1).Value
                End If
            End If
        End With
    Next Cell
End Sub