Mandatory Column Validation in Excel

[shobi1]

Hello Everyone,

I used the below code to make few columns mandatory in Excel. However, this works on all the sheets of the workbook but I want this validation only on a particular sheet. Can someone guide me with this
Thanks in advance. Also, please let me know if this can be done without VBA
Shobi

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)


Dim rngCell As Range
Dim lngLstRow As Long, lngTCols As Long
Dim lngRowCheck(1 To 5) As String


lngRowCheck(1) = "A"
lngRowCheck(2) = "B"
lngRowCheck(3) = "C"
lngRowCheck(4) = "D"
lngRowCheck(5) = "E"


lngLstRow = ActiveSheet.UsedRange.Rows.Count


For i = 1 To UBound(lngRowCheck)
For Each rngCell In Range(lngRowCheck(i) & "2:" & lngRowCheck(i) & lngLstRow)
If rngCell.Value = 0 Then
MsgBox ("Please enter a name in cell " & rngCell.Address)
rngCell.Select
End If
Next
Next i


End Sub

 

[maabadi]

Try this. Replace Sheet1 at the code with your Desired Sheet name:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rngCell As Range, lngLstRow As Long, lngTCols As Long
Dim lngRowCheck(1 To 5) As String, Sh1 as Worksheet
Set Sh1 = Sheets("Sheet1")
lngRowCheck(1) = "A"
lngRowCheck(2) = "B"
lngRowCheck(3) = "C"
lngRowCheck(4) = "D"
lngRowCheck(5) = "E"

lngLstRow = Sh1.UsedRange.Rows.Count

For i = 1 To UBound(lngRowCheck)
For Each rngCell In Sh1.Range(lngRowCheck(i) & "2:" & lngRowCheck(i) & lngLstRow)
If rngCell.Value = 0 Then
MsgBox ("Please enter a name in cell " & rngCell.Address)
rngCell.Select
End If
Next
Next i
End Sub

 

[shobi1]

Try this. Replace Sheet1 at the code with your Desired Sheet name:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rngCell As Range, lngLstRow As Long, lngTCols As Long
Dim lngRowCheck(1 To 5) As String, Sh1 as Worksheet
Set Sh1 = Sheets("Sheet1")
lngRowCheck(1) = "A"
lngRowCheck(2) = "B"
lngRowCheck(3) = "C"
lngRowCheck(4) = "D"
lngRowCheck(5) = "E"

lngLstRow = Sh1.UsedRange.Rows.Count

For i = 1 To UBound(lngRowCheck)
For Each rngCell In Sh1.Range(lngRowCheck(i) & "2:" & lngRowCheck(i) & lngLstRow)
If rngCell.Value = 0 Then
MsgBox ("Please enter a name in cell " & rngCell.Address)
rngCell.Select
End If
Next
Next i
End Sub

Thank you so very much.. it worked..

 

[maabadi]

Or at The Excel Try this:
First select range that you Want then
1. GO to Data Tab
2. Select Data Validation
3. At the windows appears, Select Setting Tab
4. At The Allow dropdown menu Select Custom
5. Uncheck Ignore Blank
6. At the Formula Box input this (Replace A1 with the first cell address at your range)

=NOT(ISNUMBER(A1))

7. Go to Error Alert Tab
8. At the Error Message Box input message you want. For Example:

Please Enter Name In The Cell

9. Press OK.