Help in null validations cell

abdullahsikandar · Feb 11, 2014

I need a help in a null validations on any cell

There are 5 rows and 5 columns, we have a button which checks for null values with respect to a specific column (here it should check with respect to ID and it shouldn`t leave any null values in the Date, Name, Age and the user should be able to give the null values for the cities.)

What i need is that whenever i press the validtion button it will check all the filled columns and rows that they are filled with the data if there is any cell which is empty it will show up an error message that "Please enter the [column name]"

Thanks in Advance, I also attached my file for your consideration please help me.

oldbrewer · Feb 11, 2014

[TABLE="width: 1031"]
<colgroup><col span="5"><col><col span="10"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]id[/TD]
[TD]date[/TD]
[TD]name[/TD]
[TD]age[/TD]
[TD]city[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]01/02/2014[/TD]
[TD]tom[/TD]
[TD="align: right"]25[/TD]
[TD]london[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]03/02/2014[/TD]
[TD]fred[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]this is acceptable?[/TD]
[TD][/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]05/02/2014[/TD]
[TD]bill[/TD]
[TD="align: right"]27[/TD]
[TD]leeds[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]07/02/2014[/TD]
[TD]harry[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]09/02/2014[/TD]
[TD]david[/TD]
[TD="align: right"]29[/TD]
[TD]glasgow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]on-screen message[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]id[/TD]
[TD]date[/TD]
[TD]name[/TD]
[TD]age[/TD]
[TD]city[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="colspan: 3"]fields need to be filled in[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/02/2014[/TD]
[TD]tom[/TD]
[TD="align: right"]25[/TD]
[TD]london[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]234[/TD]
[TD][/TD]
[TD]fred[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]id's[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]this is as filled in[/TD]
[TD][/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]05/02/2014[/TD]
[TD][/TD]
[TD="align: right"]27[/TD]
[TD]leeds[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]dates[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]07/02/2014[/TD]
[TD]harry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]names[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]09/02/2014[/TD]
[TD]david[/TD]
[TD="align: right"]29[/TD]
[TD]glasgow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]ages[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]does this approach seem helpful?[/TD]
[/TR]
</tbody>[/TABLE]

abdullahsikandar · Feb 11, 2014

Hi,

Yes, That`s what i wanted can you help me in that??

Thanks in advance.

bertie · Feb 11, 2014

Assuming the layout as:

Excel 2007

A

B

C

D

E

1

id

date

name

age

city

2

01/02/2014

tom

25

london

3

234

fred

26

4

345

05/02/2014

27

leeds

5

456

07/02/2014

harry

6

567

09/02/2014

david

29

glasgow

Sheet1

Try this in the ThisWorkbook module, not very elegant, but it works:

Rich (BB code):

Sub FillInBlanks()
   Dim rw As Long
   Dim col As Long
   Dim msg As String
   
   For rw = 2 To 6
      For col = 1 To 5
         
         With Sheets("Sheet1").Cells(rw, col)
            If .Value = "" Then
               msg = InputBox("Please Enter Row: " & rw & " " & Sheets("Sheet1").Cells(1, col).Value)
               .Value = msg
            End If
         End With
         
         msg = ""
      Next col
   Next rw
End Sub

abdullahsikandar · Feb 11, 2014

Thanks for your help.. This is working fine but instead of mesg box what if i want to highlight the missing cells using a color??

bertie · Feb 11, 2014

Set the Interior.ColorIndex:

Rich (BB code):

Sub FillInBlanks()
   Dim rw As Long
   Dim col As Long
   Dim msg As String
   
   For rw = 2 To 6
      For col = 1 To 5
         
         With Sheets("Sheet1").Cells(rw, col)
            If .Value = "" Then
                .Interior.ColorIndex = 3 'red
                
                
               'msg = InputBox("Please Enter Row: " & rw & " " & Sheets("Sheet1").Cells(1, col).Value)
               '.Value = msg
              
            End If
         End With
         
         msg = ""
      Next col
   Next rw
End Sub

oldbrewer · Feb 11, 2014

pipped by bertie, hoots mon !

abdullahsikandar · Feb 11, 2014

Awsome!!!!! :D ... Thanks ... bertie.. Last question what if i need to stop after showing the redcolor i mean there is some functionality after this and its not stopping after showing the red color.

bertie · Feb 11, 2014

After the line setting the interior colour put;
Exit Sub

abdullahsikandar · Feb 11, 2014

Hi, Bertie

I am attaching my code here, Can you review it and tell me where i am lacking because it always show up the message box and once i enter the value it will never stop, I want that once the mesgbox came up it will stop until the user press the save button again.

Private Sub btn_Save_Click()

Dim rw As Long
Dim col As Long
Dim msg As String

For rw = 2 To 6
For col = 1 To 5

With Sheets("Sheet1").Cells(rw, col)
If .Value = "" Then
.Interior.ColorIndex = 3 'red

msg = InputBox("Please Enter Row: " & rw & " " & Sheets("Sheet1").Cells(1, col).Value)
.Value = msg

End If
End With

msg = ""
Next col
Next rw
Dim Path As String
Dim FileName1 As String
Dim Date1 As String
Dim FileName2 As String
Path = "C:\Desktop"
formatted_time = Format(Now, "hh-mm")
formatted_date = Format(Now, "mm-dd-yyyy")
'MsgBox formatted_date
FileName1 = Range("B2")
FileName2 = Range("N2")
ActiveWorkbook.SaveAs Filename:=Path & formatted_date & ", " & formatted_time & ", " & FileName2 & ".xls", FileFormat:=xlNormal
MsgBox "File Saved on shared folder

"
ActiveWindow.Close
Dim lRow As Integer, Ans As String
lRow = 2

'Ans = MsgBox("Do you want to clear the data?", vbQuestion + vbYesNo, "Clear Data")
lRow = Sheets("EEA Referral Tracker").Range("A65536").End(xlUp).Row
If lRow > 1 Then
Range("A2:AG" & lRow).Delete
End If

' Dim SaveCommonFile As String
'Path = "C:\Users\xf37\Desktop\RCO Referrals"
'ActiveWorkbook.SaveAs Filename:=Path & ".xls", FileFormat:=xlNormal

End Sub

Help in null validations cell

abdullahsikandar

Board Regular

Excel Facts

oldbrewer

Well-known Member

abdullahsikandar

Board Regular

bertie

Well-known Member

abdullahsikandar

Board Regular

bertie

Well-known Member

oldbrewer

Well-known Member

abdullahsikandar

Board Regular

bertie

Well-known Member

abdullahsikandar

Board Regular

Similar threads

Forum statistics

Share this page

Help in null validations cell

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock