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.

 

[abdullahsikandar]

or I can say that the for loop which was running in your code if we can replace that i mean because it checks for certain columns and rows but i need to check for the columns which we enter the sheet it will be like this we are gonna import one file in excel then we will press that validate button to check either there are some null values or not.? Kindly help me.

 

[bertie]

I have changed the code so that an Input box pops up prompting the user to select a range to process.

If you want to stop the code after prompting the user for a value place the Exit Sub command after this line.

See the amended code below. I used the sample data in my earlier post for testing.

Option Explicit


Sub FillInBlanks02()
   Dim rng As Range
   Dim cell As Range
   Dim msg As String
   
   'prompt user to select range
   On Error Resume Next
      Set rng = Application.InputBox("Please Select Range to test", Type:=8)
      
      'check the user selected a range
      If rng Is Nothing Then
         MsgBox "No range selected, exiting!"
         Exit Sub
      End If
   On Error GoTo 0
   
   'loop through the selected range
   For Each cell In rng
      If cell.Value = "" Then
         msg = InputBox("Please Enter Row: " & cell.Row & " " & Sheets("Sheet1").Cells(1, cell.Column).Value)
         cell.Value = msg
         cell.Select
         Exit Sub
      End If
   Next cell
   
End Sub

 

[abdullahsikandar]

Hi Bertie,

Its working but what i need here is i dont want the user to select the range i want when we click the submit button it will automatically check the number of rows in a specific column and checks for the entire table and also shows the blank spaces or gave a message box.

Exmaple

Below is the example of what i am looking for :

over there if should pop up for Cells B3, B5 and Cells C6 and it should not pop up for A7, B7 and C7
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD] Column C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]V[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[oldbrewer]

why not keep it simple and "force" the user to complete all fields

[TABLE="width: 823"]
<colgroup><col><col><col><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]
[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]ATTENTION USER ![/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD="colspan: 4"]required fields have not been entered[/TD]
[TD][/TD]
[/TR]
[TR]
[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="align: right"]01/02/2014[/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD]london[/TD]
[TD][/TD]
[TD="colspan: 2"]the first empty field is[/TD]
[TD]id[/TD]
[TD][/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/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]
[/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]
[/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]
[/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]
[/TR]
</tbody>[/TABLE]

 

[bertie]

When determining the number of populated rows it is best to use a column with an entry in each row. In the example below I have assumed this to be column A.

You will also need to edit the range to loop through. I have assumed "A2:E" & NumberOfRows

Option Explicit


Sub FillInBlanks02()
   Dim rng As Range
   Dim cell As Range
   Dim msg As String
   Dim numRows As Long
   
   'EDIT COLUMN AND RANGE IF NECESSARY
   numRows = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
   Set rng = Sheets("Sheet1").Range("A2:E" & numRows)
   


   'loop through the selected range
   For Each cell In rng
      If cell.Value = "" Then
         msg = InputBox("Please Enter Row: " & cell.Row & " " & Sheets("Sheet1").Cells(1, cell.Column).Value)
         With cell
            .Value = msg
            .Interior.ColorIndex = 3
            .Select
         End With
         Exit Sub
      End If
   Next cell
   
End Sub