Null Value (blank cell) in a dropdown list with cellValidation(cellValue As Variant)

[rholdren]

Good Morning,

I have the code below that gives me an error when a blank cell in the dropdown list is chosen. Excel is Office 365

Public Function cellValidation(cellValue As Variant)
If cellValue = 3 Or cellValue = 12 Or cellValue = 15 Or cellValue = 48 Or cellValue = 51 Or cellValue = 60 Then
cellValidation = 1
Else
cellValidation = 0
End If
End Function

The list associated with this has some 5 blank cells to allow for possible expansion. When I use the combobox to change a value on the list it works fine. However, when I choose a blank cell I get an error with the line above in the red text. Run-time error '13': Type Mismatch I tried adding Or cellValue= "" I've also tried Empty , Null, Error and Nothing but that didn't work.

Any help would be greatly appreciated.

Thanks

 

[rlv01]

Why not just remove the blanks from the list until you are ready to expand the list? It's a lot cleaner to use VBA to refresh the validation range when you add a new item to the list. One example:

Private Sub UpdateValidation()
    Dim WB As Workbook
    Dim WS As Worksheet, DVWS As Worksheet
    Dim ValidationCellRange As Range, ValidationListRange As Range
    
    Set WB = ThisWorkbook
    Set WS = ActiveSheet ' or specific sheet where cells to be validated are located
    Set DVWS = ActiveSheet ' or specific sheet where cells continaing the validation list is located
    
    'Define range of cells to be validated
    With WS
        Set ValidationCellRange = .Range("A2:A" & .Range("A" & .Rows.count).End(xlUp).Row).Offset(, 1)
    End With
    
    'Define column  of cells containing the validation list
    With DVWS
        Set ValidationListRange = .Range("G2:G" & .Range("G" & .Rows.count).End(xlUp).Row)
    End With
    
    'Clear existing validation
    ValidationCellRange.Validation.Delete
    
    'Refresh cell validation
    With ValidationCellRange.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & ValidationListRange.Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

 

[rholdren]

Why not just remove the blanks from the list until you are ready to expand the list? It's a lot cleaner to use VBA to refresh the validation range when you add a new item to the list. One example:

Private Sub UpdateValidation()
    Dim WB As Workbook
    Dim WS As Worksheet, DVWS As Worksheet
    Dim ValidationCellRange As Range, ValidationListRange As Range
   
    Set WB = ThisWorkbook
    Set WS = ActiveSheet ' or specific sheet where cells to be validated are located
    Set DVWS = ActiveSheet ' or specific sheet where cells continaing the validation list is located
   
    'Define range of cells to be validated
    With WS
        Set ValidationCellRange = .Range("A2:A" & .Range("A" & .Rows.count).End(xlUp).Row).Offset(, 1)
    End With
   
    'Define column  of cells containing the validation list
    With DVWS
        Set ValidationListRange = .Range("G2:G" & .Range("G" & .Rows.count).End(xlUp).Row)
    End With
   
    'Clear existing validation
    ValidationCellRange.Validation.Delete
   
    'Refresh cell validation
    With ValidationCellRange.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & ValidationListRange.Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

I'm getting Sub or function not defined on this line
status = cellValidation(cellValue)

 

[rlv01]

I'm getting Sub or function not defined on this line
status = cellValidation(cellValue)

If you do what I suggested you can just delete your cellValidation function because you will not need it. You will not need it because your validation list will not have any blanks in it.

 

[rholdren]

I'm getting Sub or function not defined on this line
status = cellValidation(cellValue)

that would be the easiest, probably the best idea as well. I considered it before the error but kept it open in case we add another machine and I have to hand it off to someone else. Thank you for your time I really do appreciate it.