1 Replies - 328 Views - Last Post: 10 May 2019 - 03:16 AM Rate Topic: -----

#1 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 06-March 09

How to create dropdownlist with source from another sheet in excel

Posted 09 May 2019 - 10:23 PM

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


Is This A Good Question/Topic? 0
  • +

Replies To: How to create dropdownlist with source from another sheet in excel

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6798
  • View blog
  • Posts: 28,097
  • Joined: 12-December 12

Re: How to create dropdownlist with source from another sheet in excel

Posted 10 May 2019 - 03:16 AM

Please provide a clear question. What does your code do? Or fail to do? Are there errors? Etc..

But your Formula1 for the validation is missing an exclamation ! point.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1