none
Problem in dynamically add and delete Data Validation in vb.net

    Question

  • Hello,

    I have been working on this problem for hours now. I've tried and looked everywhere but doesn't find the answer

     

    I'm trying to add and delete Data Validation (as well as the incelldropdown) to a range of cells. It's working OK for the first time I put it. But I always get error if I want to do it the second, third, etc.

    This is the error I'm getting: Exception from HRESULT: 0x800A03EC

    This is the code:

    If type = "a" then

        Me.Range("A1:A20").Validation.Delete()

        Me.Range("A1:A20").Validation.Add(Type:=Excel.XlDVType.xlValidateList, _

            AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, _

            [Operator]:=Excel.XlFormatConditionOperator.xlBetween, _

            Formula1:="=named_range_1")

    elseif type = "b" then

         Me.Range("A1:A20").Validation.Delete()

        Me.Range("A1:A20").Validation.Add(Type:=Excel.XlDVType.xlValidateList, _

            AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, _

            [Operator]:=Excel.XlFormatConditionOperator.xlBetween, _

            Formula1:="=named_range_1")

    endif 

    Anyone deals with this problem before? Thanks!

    Tuesday, November 06, 2012 9:31 PM

Answers

  • I just tried running your code.  All I created was an Excel add-in, with a ribbon item that has one button.  The button's code is:

        Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
            Dim range = CType(Globals.ThisAddIn.Application.ActiveSheet, Excel.Worksheet).Range("A1:A20")
            range.Validation.Delete()
            range.Validation.Add(Type:=Excel.XlDVType.xlValidateList, _
            AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, _
            [Operator]:=Excel.XlFormatConditionOperator.xlBetween, _
            Formula1:="=named_range_1")
        End Sub

    Here's what I found:  if I create a new document, create a named_range_1 based on column "C", and then click the button, everything works wonderfully.  And everything continues to work even if I click the button many times.

    If I don't create the named range, or if I then delete it, I get the same exception as you do.

    Are you sure there's not some other code you're running that somehow deletes that named range?


    Michael Zlatkovsky | Program Manager, Visual Studio Tools for Office & Apps for Office

    Tuesday, November 06, 2012 11:43 PM