How to create dropdownlist with source from another sheet in excel using VB.NET

See more:

VB

Visual-Studio

VS2010

MS-Excel

, +

I have a program that can create a workbook. Inside a workbook is the Sheet1 and Sheet2. Sheet1 has dropdownlist control created programmatically using vb.net. it's working fine when this line of code is like this:Formula1:="=$A$1:$E$1" but when I change it to Formula1:="=Sheet2$A$1:$E$1" it returns to an error.

This is the error showed:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.VisualBasic.dll
Additional information: Exception from HRESULT: 0x800A03EC


Please help fix this kind of error. Thanks

What I have tried:

My code:

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        Dim xlApp As New Excel.Application
        xlApp.Visible = True
        Dim xlWorkbooks As Excel.Workbooks = xlApp.Workbooks
        Dim xlWorkbook As Excel.Workbook = xlWorkbooks.Add
        Dim xlWorksheet As Excel.Worksheet = CType(xlWorkbook.Worksheets(1), Excel.Worksheet)

       


        Dim xlRng As Excel.Range = xlWorksheet.Cells(5, 5)

        xlRng.Select()  
        With xlApp.Selection.Validation   
            .Add(Type:=Excel.XlDVType.xlValidateList, _
             AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, _
            Operator:=Excel.XlFormatConditionOperator.xlBetween, _
             Formula1:="=Sheet2$A$1:$E$1")
            .IgnoreBlank = True
            .InCellDropdown = True
        End With
    

        With (xlWorkbook) 
            .Worksheets.Add(After:=.Worksheets(1))
            .Sheets("Sheet2").Name = "Sheet2"
        End With
        With xlWorkbook.Worksheets("Sheet2") 
            .Cells(1, 1) = "Jose"
            .Cells(1, 2) = "Juan"
            .Cells(1, 3) = "acronym"
            .Cells(1, 4) = "sector"
            .Cells(1, 5) = "type_acronym"

            .Cells(2, 2) = "(Select Agency Name)"
            .Cells(3, 1) = "2198"
            .Cells(3, 2) = "ABRA STATE INSTITUTE OF SCIENCE AND TECHNOLOGY"
            .Cells(3, 3) = "ASIST"
            .Cells(3, 4) = "State Universities and Colleges"
            .Cells(3, 5) = "Proper"
        End With


        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRng)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbooks)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
    End Sub