배열을 사용하여 Drop Down List 만들기

 

Excel VBA to Create Data Validation List from Array

Code

Sub data_validation_from_array()

        Dim region, product As Variant
        Dim region_range, product_range As Range
        
        region = Array("A4", "A3", "A2", "A1", "A0", "A02")
        product = Array("not", "BOM")
        
        Set region_range = Range("C5:C10")
        Set product_range = Range("D5:D10")
        
        With region_range.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(region, ",")
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = "Error"
            .InputMessage = ""
            .ErrorMessage = "Please Provide a Valid Input"
            .ShowInput = True
            .ShowError = True
        End With
        
        With product_range.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(product, ",")
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = "Error"
            .InputMessage = ""
            .ErrorMessage = "Please Provide a Valid Input"
            .ShowInput = True
            .ShowError = True
        End With
        

End Sub

---