Mandatory Column Validation in Excel

[sanjaynaidubas]

hi,
i am Trying to Create an Excel sheet that will have Few columns
R1 will have the names of the columns
suppose i have 5 columns say

Name | DOB | ADDRESS |Contact | Member

i want to make Name and DOB column mandatory.
this xls may contain only 1 record or 100 or 1000
but this validation should work for records for which some data is entered.

I tried using VB script.. but am able to make only particular Cell as mandatory not the column.
Please help

thanks in Advance

Regards,
Sanjay Naidu

 

[Jeffrey Lopez]

Sorry I'm new to VB, and i,m struggling with this code. After implementation everything is perfect but excel shows the msg continuously till the end of loop and i just want to show it one by one means it is not user frndly i just want to do it like once msg shown to user, user can fill that particular cell and again see the msg if any cell remain empty.
Thanks in advance

Easy enough. You just wanted to add an "Exit Sub" So replace this:

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

With this:

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
            Exit Sub
        End If
    Next
Next i

this will stop the macro where the missing value is and select the cell that is missing. Hope that helps!

 

[Ashu Rai]

Can you please help me with this?

am trying to make the column mandatory in a excel sheet where there is below column

Employee EID Employee Name Effective date

i want to make entire effective date column mandatory as the number of employee may vary from 10 -1000.

can you please help me with this?

 

[Jeffrey Lopez]

Can you please help me with this?

am trying to make the column mandatory in a excel sheet where there is below column

Employee EID Employee Name Effective date

i want to make entire effective date column mandatory as the number of employee may vary from 10 -1000.

can you please help me with this?

Hey Ashu, welcome to Mr. Excel!

My first set of code should work for you, all you need to do is change the line:

[COLOR=#333333]For Each rngCell In Range("A1:A" & lngLstRow)[/COLOR]

Change it from A1:A, to whatever column you need.

 

[Ambibambi]

Hi,

I tried to use the code mentioned above:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


Do Until IsEmpty(ActiveCell)


ActiveCell.Value = ActiveCell.Value + 1
ActiveCell.Offset(1).Select






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
Exit Sub
End If
Next
Next i




Loop


End Sub


However, the code doesn't stop until i've completely filled out all the rows with the required columns. Is there a way for it identify the last row that has some data in it and require my required columns to be filled out?

Thanks in advance!

 

[Ambibambi]

My apologies, this is the code im working with

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
Exit Sub
End If
Next
Next i




Loop


End Sub

 

[shobi1]

Glad it help! You can add multiple columns by adding another loop and an array. Try something like this:

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

This use an additional loop:

 For i = 1 To UBound(lngRowCheck)

So to set your columns to check, you will need to change this line for the total NUMBER of columns:

 Dim lngRowCheck(1 To 5) As String

Then just either add or remove the declarations:

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

Everything else should work as before. Please be sure you back up your excel workbook before you make any changes or run any macros. Also I am not currently at a machine that I can test, so if this does not work, please let me know.

Hello Jeffrey
Thank you so much for this.. this works..
One quick question, this currently works on the all the sheets of the workbook. how do I get this to work only for a particular sheet?

 

[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
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

 

[Peter_SSs]

@shobi1

Welcome to the MrExcel board!

Duplicate to: Mandatory Column Validation in Excel

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in your own (linked) thread only.