CODEWITHSUNDEEP
excelvbafunctionvalidation
cycla
cycla

Reputation: 147

How to create a macro that creates various validation lists according to condition vba

I want to make a macro that makes list that corresponds to certain condition. So this is the case: If the referenced cell value corresponds to "Annually", I would want to have a list that only allows Annually, If the referenced cell value corresponds to "Semi-Annually", then that list would allow Semi-Annually and Quarterly. If the referenced cell value corresponds to "Quarterly", then list would allow Semi-Annually, Quarterly, and Annually. So this is my code:

Sub listcustom()
Dim wb As Workbook, wsh As Worksheet
Dim lastRow As Long
Set wb = ThisWorkbook
lastRow = wb.Worksheets("Input").Range("A" & Rows.count).End(xlUp).Row
    For i = 5 To lastRow
        If wb.Worksheets("Input").Cells(i, 11) = "Annually" Then
            wb.Worksheets("Input").Cells(i, 14).Value = "Annually"
        ElseIf wb.Worksheets("Input").Cells(i, 11) = "Semi-Annually" Then
            wb.Worksheets("Input").Cells(i, 14).Validation.add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Formula1:="=$Z$16:$Z$17"
        ElseIf wb.Worksheets("Input").Cells(i, 11) = "Quarterly" Then
            wb.Worksheets("Input").Cells(i, 14).Validation.add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Formula1:="=$Z$16:$Z$18"
        End If
    Next i
End Sub

Starting from 5th row(since i = 5 at first), it works when it Put Cell(5,11) as Annually -> the output is Annually. The 6th row(i = 6) I put Cell(6,11) as Semi-Annually, and the two options: Semi-annually and annually came out. However starting from the 3rd loop, which is i = 7, I wrote Cell(7,11) as Quarterly. Then the vba would reply, application defined or object defined error. I don't get why there is a problem with this 3rd loop. If there is a problem with the code itself, 1st and 2nd loop shouldn't be working at first, isnt' it?

FYI, Z16~Z18 refers to Annually, Semi-Annually, and Quarterly.

Thanks

Upvotes: 0

Views: 102

Answers (1)

Tim Williams
Tim Williams

Reputation: 166136

You cannot set a validation list using a function called from a worksheet cell - functions called this way can only return a value: they can't have "side effects" such as setting a validation rule, etc.

See: https://support.microsoft.com/en-us/topic/description-of-limitations-of-custom-functions-in-excel-f2f0ce5d-8ea5-6ce7-fddc-79d36192b7a1

You can maybe look at using the Worksheet_Change event, then refactor your code as a Sub and call if from the event handler.

Public Sub listcustom(valueCell As Range, listCell As Range)
     Dim frm As String
     Select Case valueCell.Value
         Case "Annually": frm = "=$Z$16"
         Case "Semi-Annually: frm = "=Z16:Z17"
         Case "Quarterly": frm = "=Z16:Z18"
         Case Else: Exit Sub
     End Select

     With listCell.Validation
         .Delete
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                  Operator:= xlBetween, Formula1:="=$Z$16"
     End With
End Sub

Upvotes: 1

Related Questions