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]

Welcome to the Mr. Excel Message Boards!

Someone else might come up with a more elegant way to ensure all values are filled... but I would just do a check before the save... Something along the lines of:

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


Dim rngCell As Range
Dim lngLstRow As Long


lngLstRow = ActiveSheet.UsedRange.Rows.Count


For Each rngCell In Range("A1:A" & lngLstRow)
    If rngCell.Value = 0 Then
        MsgBox ("Please enter a name in cell " & rngCell.Address)
        rngCell.Select
    End If
Next
End Sub

All this does it check to makes sure there is no null value in Column and if so, it pops up a box and says where it is, and selects it... but won't save till all values are filled in...

This is a "Workbook" macro, so needs to put into "ThisWorkbook" code...

Hope that helps, and if you have any questions, just let me know!

 

[sanjaynaidubas]

Thank you Jeffrey.. this works for me
thank you so much.

 

[Jeffrey Lopez]

Great, thanks for letting us know!

 

[Eurenia]

Hi!

The code worked great for me! However, how do I edit the code such that I can make multiple columns mandatory? (Sorry, new to VBA )

I've tried searching the internet for answers but I can't seem to get it! Would really appreciate if anyone could help me with this.

 

[Jeffrey Lopez]

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.

 

[Eurenia]

This works great! But does this work with consecutive columns only?

 

[Jeffrey Lopez]

This works great! But does this work with consecutive columns only?

No, it works with whatever column letters you add to the array. So it could be A, C, G, S,T if you wanted

 

[Eurenia]

I've tried with B, D, E, F and from column C onwards, it started checking the whole of row 2 instead of D, E and F column.

 

[abhi1309]

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