Mandatory Column Validation in Excel

Status
Not open for further replies.

sanjaynaidubas

New Member
Joined
Jul 23, 2013
Messages
5
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
 
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:

Code:
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:
Code:
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!
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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?
 
Upvote 0
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:

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

Change it from A1:A, to whatever column you need.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
Glad it help! You can add multiple columns by adding another loop and an array. Try something like this:

Code:
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:

Code:
 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:

Code:
 Dim lngRowCheck(1 To 5) As String

Then just either add or remove the declarations:

Code:
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?
 
Upvote 0
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
 
Upvote 0
@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.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top