Excel 2007 Data Validation Input Message using VB.net
By: Issac
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.
Archived Comments
1. Billyweimb
View Tutorial By: Billyweimb at 2017-06-02 10:11:18
2. Being a freshman in blogging, I really appreciate such resources where people write constructive pos
View Tutorial By: tyu at 2009-09-03 08:12:32
Comment on this tutorial
- Data Science
- Android
- AJAX
- ASP.net
- C
- C++
- C#
- Cocoa
- Cloud Computing
- HTML5
- Java
- Javascript
- JSF
- JSP
- J2ME
- Java Beans
- EJB
- JDBC
- Linux
- Mac OS X
- iPhone
- MySQL
- Office 365
- Perl
- PHP
- Python
- Ruby
- VB.net
- Hibernate
- Struts
- SAP
- Trends
- Tech Reviews
- WebServices
- XML
- Certification
- Interview
categories
Related Tutorials
Using Resume Next and Resume Line in VB.net
Using On Error GoTo 0 in VB.net
Getting an Exception's Number and Description in VB.net
Raising an Exception Intentionally in VB.net
Exception Filtering in the Catch Block in VB.net
Using Multiple Catch Statements in VB.net
Throwing an Exception in VB.net
Throwing a Custom Exception in VB.net
Changes in Controls from VB6 to VB.net
Unstructured Exception Handling in VB.net