Validating a cell value excel macro
I wish to run the validation while someone tries to save the macro enabled excel file. And the VBA macro code goes below: Private Sub Workbook_Before Save(By Val Save As UI As Boolean, Cancel As Boolean) Dim error Code As Integer Dim error Msg As String Dim num Of Recs As Integer Dim counter As Integer '--------------------- 'Start - Validate Date '--------------------- Sheet1. Select error Code = 0 num Of Recs = 0 Do Until Active Cell. For accuracy and official reference refer to MS Books On Line and/or MSDN/Tech Net.
Value = vb Null String ' Validate Date If Is Date(Active Cell. My employer do not endorse any tools, applications, books, or concepts mentioned on the blog.
For instance, let's say that you add a fourth choice of "Provide Answer." You could then change the value in the cell to the same value and save your workbook.
When the user opens it, the cell contains "Provide Answer" and, once they select the cell, they won't be able to blank it out; they will need to provide an answer.
The entire first column now contains a drop down list in each cell.
If you can provide your guidance on this issue, that will be great.Also to avoid this manual work to identify the incorrect/invalid data out of thousands of records automated approach would be quite helpful. Following are the fields that contain invalid values." _ & vb Cr Lf & error Msg & vb Cr Lf & vb Cr Lf & _ "Please correct the values highlighted in RED color.", vb Critical, "Data Validation ERROR" Cancel = True End If End Sub Now save the macro code and close the editor.Excel not only store data but also have a power to run VBA macro code against and manipulate the data. Now try to Save the Excel file, you will get the cells with invalid values highlighted in RED, as shown in the image.The problem is that when someone activates the cell, it is possible for them to still leave it empty.The only time that Excel won't allow the person to leave the cell blank is if they start to edit the cell and try to leave it blank after the edit.
Merle wants, once the cell is selected, for the user to absolutely only be able to leave the cell if they choose Yes, No, or N/A.