Unable to clear the contents of Datavalidation in excel using VBA

[teja_98666]

Hi,

I have a VBA function that will bind an array to excel range and in turn use that range to specify a data validation list for the selected range.

I want to remove the datavalidation if the array that i'am passing is empty but this doesnt happen, i can still see the dropdown of previous values when i select any cell.
Below is the sample code that replicates the issue. I already have datavalidation available in certain cells, so when i click on the cell the validation should be removed.


Any help is appreciated.

[FONT=Verdana]Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal pobjRange As Range)[/FONT][/FONT]

Application.EnableEvents = False


Dim pvarArrayEntries As Variant


ReDim pvarArrayEntries(0, 1)


If CreateDataValidation(pobjRange, pvarArrayEntries) = True Then
    'blah blah
End If


Application.EnableEvents = True
    
End Sub


Private Function CreateDataValidation(ByVal pobjRange As Range, ByVal pvarArrayEntries As Variant) As Boolean
    
    Const PICKLIST_COLUMN = "IV"
    
    CreateDataValidation = True
    
    On Error Resume Next
    
    pobjRange.Validation.Delete
    
    pobjRange.ClearContents
    pobjRange.Value = ""
      
    If UBound(pvarArrayEntries) = 0 Then
        pobjRange.Validation.Delete
    Else
        '
        ' Select Range
        '
        Dim lobjPicklistRange As Range
        Set lobjPicklistRange = Application.ActiveSheet.Range(Cells(1, PICKLIST_COLUMN), Cells(UBound(pvarArrayEntries) + 1, PICKLIST_COLUMN))
        '
        ' Clear existing Column
        '
        lobjPicklistRange.Clear
        '
        ' Write entries to the picklist column
        '
        lobjPicklistRange = pvarArrayEntries
        
        Dim lstrFormula As String
        lstrFormula = "=" & PICKLIST_COLUMN & "1:" & PICKLIST_COLUMN & UBound(pvarArrayEntries) + 1
        
        If Application.ReferenceStyle = xlR1C1 Then
            lstrFormula = Application.ConvertFormula(lstrFormula, xlA1, xlR1C1)
        End If
            
        With pobjRange.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=lstrFormula
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    
    End If
   
End Function

 

[teja_98666]

Any body ??

 

[Perpa]

Teja,

Just glancing at your code, could it be the line in your function that reads:

    If UBound(pvarArrayEntries) = 0 Then

might need to be:

    If UBound(pvarArrayEntries) = [COLOR="#FF0000"]""[/COLOR] Then

Worth a try?
Good luck!
Perpa

 

[teja_98666]

Hi Perpa,
Thanks for the reply.
I tried that but it didn't work.

 

[RoryA]

I'd start by removing the On Error Resume Next and see what's really going on.

 

[teja_98666]

Hi,
I could find a workaround for this problem, by setting InCellDropdown to false, this way if the array has no elements, by setting the above property to false the drop down will not be visible and user will not be able to set any value. Thanks all for you inputs