Help in null validations cell

[abdullahsikandar]

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]

id

date

name

age

city

123

01/02/2014

tom

25

london

234

03/02/2014

fred

26

this is acceptable?

345

05/02/2014

bill

27

leeds

456

07/02/2014

harry

28

567

09/02/2014

david

29

glasgow

on-screen message

id

date

name

age

city

4

fields need to be filled in

01/02/2014

tom

25

london

234

fred

26

1

id's

this is as filled in

345

05/02/2014

27

leeds

1

dates

456

07/02/2014

harry

1

names

567

09/02/2014

david

29

glasgow

1

ages

does this approach seem helpful?

<colgroup><col span="5"><col><col span="10"></colgroup><tbody>
</tbody>

 

[abdullahsikandar]

Hi,


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

Thanks in advance.

 

[bertie]

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:

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]

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]

Set the Interior.ColorIndex:

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]

pipped by bertie, hoots mon !

 

[abdullahsikandar]

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]

After the line setting the interior colour put;
Exit Sub

 

[abdullahsikandar]

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