dcsimg
Results 1 to 4 of 4

Thread: How to create dropdown list with source from another sheet in excel using vb.net

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2019
    Posts
    30

    How to create dropdown list with source from another sheet in excel using vb.net

    My code:

    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 xlApp.Selection.Validation    ' change range to selection
            '.Add(Type:=Excel.XlDVType.xlValidateList, _
            'Formula1:="=Sheet1!$B$2:$B$2967")
            '.IgnoreBlank = True
            '.InCellDropdown = True
            'End With
    
            With (xlWorkbook) 'I put this code separately for adding Sheet2 because it will get an error when I put this on above code "with xlworkbook"
                .Worksheets.Add(After:=.Worksheets(1))
                .Sheets("Sheet2").Name = "Sheet2"
            End With
            With xlWorkbook.Worksheets("Sheet2") 'This code is a Sheet1 as stated above code (xlWorkSheet = xlWorkBook.Sheets("Sheet1")
                .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
    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

    line of code where the error happened:
    Code:
     With xlApp.Selection.Validation    ' change range to selection
                .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
    Image of error: Name:  qq.jpg
Views: 103
Size:  17.3 KB

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,090

    Re: How to create dropdown list with source from another sheet in excel using vb.net

    Hi

    change this line
    Code:
      Dim xlRng As Excel.Range = xlWorksheet.Cells(5, 5)
    to

    Code:
     Dim xlRng As Excel.Range = CType(xlWorksheet.Cells(5, 5), Excel.Range)
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Apr 2019
    Posts
    30

    Re: How to create dropdown list with source from another sheet in excel using vb.net

    Quote Originally Posted by ChrisE View Post
    Hi

    change this line
    Code:
      Dim xlRng As Excel.Range = xlWorksheet.Cells(5, 5)
    to

    Code:
     Dim xlRng As Excel.Range = CType(xlWorksheet.Cells(5, 5), Excel.Range)
    Thank you my friend for the suggestion but same error shows. I think that changing of the code above has nothing to do with what I have asked for.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Apr 2019
    Posts
    30

    Resolved Re: How to create dropdown list with source from another sheet in excel using vb.net

    Thanks it solved now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width