VSTO Created File does not trigger data validation event

Question

Hi all,

I've just created an excel file with VSTO and added some data validation rules as in-cell-dropdowns.
But the problem is, even i've setted it to only allow list of values from a range that i've specified (referred as name), it allows all values.

I've protected my sheet and unlocked the cells which has validation.

The greater part is, when i open excel manually and do the same validation scenario, it works.

Here is the code that i've been using in VSTO:

            'For CurrencyList
            If Not Globals.ThisWorkbook.Names.Item("CurrencyArea").RefersToRange Is Nothing Then
                xlRange = Globals.ThisWorkbook.Names.Item("CurrencyArea").RefersToRange

                With xlRange.Validation
                    .Delete()
                    .Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, Formula1:="=CurrencyList")
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = "Read-Only Cell"
                    .InputMessage = ""
                    .ErrorMessage = GeneralClass.ReadOnlyCellMsg
                    .ShowInput = False
                    .ShowError = True
                End With
                xlRange.Locked = False
                Globals.Msh.Protect("myPassword", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering:=True)
            End If

---

BI and ERP Consultant @ Nexum Bogazici Personal Blog: http://iszekasinedir.wordpress.com/

Answer 1

Hello,

<<But the problem is, even i've setted it to only allow list of values from a range that i've specified (referred as name), it allows all values.>>
I have used the code above in my side. When I debug this project, I can see each cell of CurrencyArea name range has a data validation, and it does not allows to input data except the values in dropdown list. Regarding this scenario, in your side, do you see the dropdown arrow when selecting the cell in CurrencyArea? If not, I think it may be caused by CurrencyList in your code.

For test, I create an Excel workbook project, defined a name range (C1-C9) on sheet1, and input values to this name range by UI. The code I use like this

       If Not Globals.ThisWorkbook.Names.Item("CurrencyArea").RefersToRange Is Nothing Then
            xlRange = Globals.ThisWorkbook.Names.Item("CurrencyArea").RefersToRange

            Dim _LookupRange As Excel.Range = Me.Application.Range("C1", "C9")
            Dim reference As String = New StringBuilder("=").Append(_LookupRange.Address(True, True, Excel.XlReferenceStyle.xlA1, True, True)).ToString
            Dim _Name As Excel.Name = Me.Application.Names.Add("CurrencyList", reference)

            With xlRange.Validation
                .Delete()
                .Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, Formula1:="=CurrencyList")
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = "Read-Only Cell"
                .InputMessage = ""
                '.ErrorMessage = GeneralClass.ReadOnlyCellMsg
                .ShowInput = False
                .ShowError = True
            End With
            xlRange.Locked = False
            Globals.Sheet1.Protect("myPassword", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering:=True)
        End If

If I have mistaken this scenario, please point me out.

Best regards,
Bessie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

We are changing the issue type to "General Discussion" because you have not followed up with the necessary information. If you have more time to look at the issue and provide more information, please feel free to change the issue type back to "Question" by opening the Options list at the top of the post window, and changing the type. If the issue is resolved, we will appreciate it if you can share the solution so that the answer can be found and used by other community members having similar questions.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

Hi,

Sorry for late answer but at last i've found the solution. It is by design i guess.

Well, if you set up a validation formula, "Name" i.e CurrencyArea
be sure that CurrencyArea range doesnt contain null cells. When there are nulls in the CurrencyArea range, cell with a validation allows everything!

Here is the part that i copied from Excel's Help:

If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.
 
Best Regards.

---

BI and ERP Consultant @ Nexum Bogazici Personal Blog: http://iszekasinedir.wordpress.com/