In the following example, the user has typed abc , which is not an acceptable value in that cell.
The name of this feature in MS Excel is Data Validation.'Therefore we use the property "Formula" 'instead of "Value". Address & " has a comment." End If Before Exit: Set r Cell = Nothing Exit Sub Error Handle: Msg Box Err.If you used "Value", 'it would say the cell is empty, even if 'it contains spaces/blanks. 'If it isn't a numeric value or an error like division 'by zero, we assume it is text of the data type String. Description & " Error in procedure Cell Check." Resume Before Exit End Sub In the examples above I have written "r Cell. Strictly speaking that is not necessary, because "Value" is a cell's default property, so I could have achieved the same just be writing "r Cell". Add Type:=xl Validate List, Alert Style:=xl Valid Alert Stop, Operator:= _ xl Between, Formula1:=AStr . How to add VBA code to your comment: [vb 1="vbnet" language=","] VBA code [/vb] How to add a picture to your comment: Upload picture to Add picture link to comment.I want to ensure that a user inputs "dd-MMM-yyyy" (eg. how do i use VBA to validate that this is true, and if not, return an error msg box. Value = Format(dtm, "dd-mmm-yyyy") End With Next rng Cell ' Turn on events Application. Shoud be: format of the cell (or set the numberformat of the entire range and leave out the . "The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. Private Sub Worksheet_Selection Change( Do you need to have the user to enter the data thos way or do you need the date formatted this way for later calculations? Enable Events = True On Error Go To 0 End If End Sub out the . You can use data validation to restrict the type of data or values that users enter into cells.For example, you might use data validation to calculate the maximum allowed value in a cell based on a value elsewhere in the workbook.When you work with VBA (macros) in Excel, you often need to test or check cell content. If you mark the code with the mouse, you can copy (CTRL C) and paste (CTRL V) it into a VBA module.Is the cell empty, is it a number, a text (string), a formula, an error, a date - or does it have a comment or conditional formatting? The examples use cell A1 in the active sheet, but any cell or larger range can be used.