[RESOLVED] Excel Custon Validation Formula

Hi All,

I'm writing a xlsx file directly from vb.net
I have a problem when I try to add a cell custom validation formula with this istruction:

HTML Code:

---

Sub AddValidationFormula(byval range as range)
  dim validationFormula as string = "=mod(C2,F2)=0"
  range.Validation.Add(XlDVType.xlValidateCustom, XlDVAlertStyle.xlValidAlertStop, validationFormula)
End Sub

---

The validationFormula above indicated is an semplified formula that I have try to used to solve my problem.

When I run my code, it crashes with "exception from HRESULT: 0x800A03EC".
I have also tryed the italian Fornula ("=RESTO") but without result.

If I write the formula directly in the Excel file, I don't have any problem.

I have attached the stackTrace error

Can anyone help me?

Thanks

Marcello

add a semicolon in your Formula not a comma

in Germany it would look like this

Code:

---

=REST(12;2)=0

---

or

Code:

---

=REST(-3;2)

---

would return 1

I have tryed using also the semicolon, but I have the same error.

I also tryed a formula using values instead of cells, but the error is always the same

I'm in dubt if I need to specify the formula (only for validation) in italian (RESTO) or english (MOD).

Using the formula without the final "=0" in a single cell formula, I don't have any problem (in this case I have used the italian formula and the FormulaLocal property)

Marcello

well I tried this this and it worked

Code:

---

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
            Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
            xlWb = xlApp.Workbooks.Open("D:\TestFolder\vbexcel.xlsx")
            Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle3"), Worksheet)

            With xlSt
                .Range("G22").FormulaLocal = "=REST(C2;F2)=0"
            End With
            xlWb.Save()
            xlApp.Quit()
            xlApp = Nothing
        Catch g As Exception
            MessageBox.Show(g.ToString)
        End Try
    End Sub

---

Hi ChrisE,

with the code that you propose, you set a formula in a single cell (in the example, in the cell G22).

I need to add a validation formula (range.validation.add) to this cell, like the Data Validation inserted from Excel using menu data --> data validation.

In another cell, i Have set a data validation data, using the XlDVType.xlValidateTextLength and I don't have any problem.

Thanks.

Marcello

I'm resolved.

I'm rewrite entire class using the Epplus library.
With this library, I was able to set the formula for validation.

Thanks to everyone who tried to help me.

Marcello