Data validation automatically set to first item in list

dirtychinchilla

Board Regular
Joined
Jun 21, 2013
Messages
234
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
The macro shouldn't really effect the way that this works. Basically, the contents of the data validation box change based on which product is selected. This is done (in short) by renaming ranges to get the right information. It's after this stage that I'd hope to be able to select the first item in the list.

I hope that's helpful!
 
Upvote 0
This is the code used with the data validations. Other than this, they're just set up as normal. Each data validation cell is equal to a range, which is the always the range with the full name ie Filters, Control_Panels etc

Code:
Sub Update_Selection()' Update_Selection Macro


    Range("Selections").ClearContents


    With Sheets("Back")
        .Range("Units").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("Criteria"), _
        CopyToRange:=.Range("Extract"), Unique:=True
        .Range("F_UnitName").Copy
    End With
    
    Sheets("Main").Range("Name").PasteSpecial Paste:=xlPasteValues
    Sheets("Main").Range("Options").Cells.SpecialCells(xlCellTypeAllValidation).ClearContents
    
    Dim rngDst As Range
    Dim rngSrc As Range
    Dim arr As Variant
    Dim I As Long
    
    Application.ScreenUpdating = False
    
    arr = Array("F", "F_Target", "Filters", "CP", "CP_Target", "Control_Panels", _
                "M", "M_Target", "Mounting", "CM", "CM_Target", "Cooling_Modules", _
                "HS", "HS_Target", "Heating_Surfaces", _
                "S", "S_Target", "Sensors", "BMS", "BMS_Target", "BMS", _
                "CS", "CS_Target", "Condensation", "EM", "EM_Target", "Energy_Meter", _
                "PWO", "PWO_Target", "Panels_WO", "PW", "PW_Target", "Panels_W", _
                "OG", "OG_Target", "Outside_Grilles", "FC", "FC_Target", "Facade_Cover")


    For I = LBound(arr) To UBound(arr) Step 3


        With Sheets("Options")
        
            Set rngSrc = .Range(Replace(Sheets("Main").Range("UnitType").Value, " ", "_") & "_" & arr(I))


            Set rngDst = .Range(arr(I + 1))
            
        End With


        rngSrc.Copy


        rngDst.PasteSpecial xlPasteValues


        With ActiveWorkbook.Names(arr(I + 2))
            .Name = arr(I + 2)
            .RefersToR1C1 = "=Options!" & rngDst.Resize(rngSrc.Rows.Count, 1).Address(, , xlR1C1)
        End With
        
        Application.DisplayAlerts = False


    Next I
    
    'If Range("UnitType").Value = "AM 100" _
    'Then Range("AM_100_Prices") = Range("Base_Price")
       
    
    Range("Description_Target").Select
    Range("Description_Target").MergeArea.ClearContents
    Selection.MergeCells = False
    Range("Price_Target_A").Value = "-"
    Range("Unit_Price_Target_A").Value = "-"
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
    Application.Goto Sheets("Main").Range("Name")


End Sub
 
Upvote 0
Sorry, obviously I haven't explained it very well.

The data validation isn't set in the VBA. I have set it up previously, using the button in the Data tab. However, the ranges that you see in the array there are the lists used in the data validation.

If it helps, I'd happily have the data validation set by VBA.
 
Upvote 0
Does this work for you?

Code:
Sub Test()
    Dim Cell As Range
    For Each Cell In ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation).Cells
        With Cell
            If .Validation.Type = xlValidateList Then
                .Value = Range(Replace(.Validation.Formula1, "=", "")).Cells(1, 1)
            End If
        End With
    Next Cell
End Sub
 
Upvote 0
Hi Andrew,

Thank you very much for that.

It works on occasion, but not always.

Currently, all data validation cells are blank, as they start out. If I run the test macro, I receive the "Method 'Range' of object '_Global' failed"

If it helps, I've named the range that I really need it to apply to, DataValidationClear.

I tried selecting a few of the cells, and then running Test, but it had the same error.

It did work a couple of times, but I have no idea how. Also, if it helps, again, the first value is always "-".
 
Upvote 0

Forum statistics

Threads
1,214,973
Messages
6,122,534
Members
449,088
Latest member
RandomExceller01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top