Programming Tutorials

Excel 2007 Data Validation Input Message using VB.net

By: Issac in VB.net Tutorials on 2009-01-31  

To embed message in Excel spread sheet we can use data validation input messages once we select the cell it will display the message, we can use .net to set those data validation input. Let's see the source code for it.

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim rangeCells As Excel.Range

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        xlWorkSheet.Range("B5", "D5").Validation.Add( _
        Type:=Excel.XlDVType.xlValidateInputOnly, _
        AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, _
        Operator:=Excel.XlFormatConditionOperator.xlBetween)
        xlWorkSheet.Range("B5", "D5").Validation.IgnoreBlank = True
        xlWorkSheet.Range("B5", "B5").FormulaR1C1 = "Click  Here " + _
        "to see Notes"
        xlWorkSheet.Range("B5", "D5").Validation.InputTitle = _
        "vb.net-informations.com"
        xlWorkSheet.Range("B5", "D5").Validation.ErrorTitle = "Error in Title"
        xlWorkSheet.Range("B5", "D5").Validation.InputMessage = "Here is " + _
        " the notes  embeded - you can enter 255 characters maximum in notes "
        xlWorkSheet.Range("B5", "D5").Validation.ErrorMessage = "Error in Notes"
        xlWorkSheet.Range("B5", "D5").Validation.ShowInput = True
        xlWorkSheet.Range("B5", "D5").Validation.ShowError = True

        xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox ("File created!")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

When you execute this source code you can find Input message in an Excel Cell.