VBA code to check multiple cells in active worksheet if required field is blank

[iAmPatch]

Hi Excel Gurus

I was browsing through the forum to search for similar answers to my obstacle: "VBA code to check multiple cells in active worksheet if required field is blank" and found these two threads...

https://www.mrexcel.com/forum/excel-questions/366226-help-making-sure-all-data-filled.html
https://www.mrexcel.com/forum/excel-questions/366611-vba-check-if-blank-cell.html

I tried to integrate them to my code, but to no avail. I either get a "working code" but doesn't really do what I want it to do. Or I get an error when trying to run the code.

My current active sheet ("HRI Form") has multiple required fields that needs to be filled out prior to the data being deleted. And I currently have this code running:

Private Sub Clear_Click()
Dim Msg As String, Ans As Variant
    Msg = "Have you printed and transferred the data to the tracker?"
    Ans = MsgBox(Msg, vbYesNo)
    Select Case Ans
        Case vbYes
        Call Clearcells
        Case vbNo
        MyMessage = "Please print and transfer data to the tracker.  Thanks! :)"
        MsgBox MyMessage
        GoTo Quit:
        End Select
Quit:
End Sub

I was planning to integrate an "IsEmpty" code to it but can't figure out how to...

The cell ranges that needs to be checked if empty would be G7:N7, G8:K8, L8:P8, L27:W27. If these ranges are blank then show message of "Please print and transfer data to the tracker. Thanks! ". Else show message of "Your data will now be wiped clean." then Call Clearcells module

Can anyone please help me.

Thanks a lot

 

[iAmPatch]

The ranges listed above are all filled out manually. While the rounded rectangles are manually changed as well throughout completing the form. The last code i posted is like a more complex “undo”; since the form is accomplished, data is transferred, form is printed then the user clicks on a button which acts like a reset button instead of closing the file and opening again.

 

[mumps]

Try:

Private Sub Clear_Click()
    Application.ScreenUpdating = False
    Dim rng As Range
    If MsgBox("Have you printed and transferred the data to the tracker?", vbYesNo) = vbYes Then
        Call Clearcells
    Else
        For Each rng In Range("G7:N7, G8:P8, L27:W27")
            If rng = "" Then
                MsgBox ("Please fill in cell " & rng.Address(0, 0) & ".")
                rng.Select
                Application.ScreenUpdating = True
                Exit Sub
            End If
        Next rng
    End If
    Application.ScreenUpdating = True
End Sub

 

[iAmPatch]

Thanks @mumps

I’ll try this when I get back to the office. Will let you lnow how it goes

 

[Fluff]

You can slim down your ClearCells code for the rounded rectangles like

Dim i As Long, Ary As Variant
Ary = Array(344, 345, 343, 346, 273, 86, 347, 89, 116, 97, 94, 103, 141, 196, 142, 226, 194, _
            198, 195, 148, 428, 319, 257, 204, 205, 370, 371, 247, 238, 176, 177, 360, 361, 362)
For i = 0 To UBound(Ary)
   With ActiveSheet.Shapes.Range("rounded Rectangle " & Ary(i)).Fill
      .Visible = msoTrue
      .ForeColor.RGB = RGB(255, 255, 255)
      .Transparency = 0
      .Solid
   End With
Next i

Just add the rest of the numbers to the array

 

[Fluff]

You can also do the same sort of the for the ranges

Sub Clearcells()
   Dim RAry As Variant, ary As Variant
   Dim i As Long
   
   RAry = Array("T3:X3", [COLOR=#0000ff]"N15:Q15"[/COLOR], "AB15", [COLOR=#0000ff]"L15:M15"[/COLOR], [COLOR=#0000ff]"K15"[/COLOR], "AB16", "T5:X5", "T7:Y7", "G7:n7", "[COLOR=#ff0000]g8:k8[/COLOR]", "[COLOR=#ff0000]l8:p8[/COLOR]", "e17:j17", _
               "k17:m17", "l27:w28", "j37:p37", "l42:m42", "r42:z46", "e45:i45""f47:y47", "i48:y48", "h53:l53", "h56:l57")
   
   For i = 0 To UBound(RAry)
      Range(RAry(i)).ClearContents
   Next i
   
   ary = Array(344, 345, 343, 346, 273, 86, 347, 89, 116, 97, 94, 103, 141, 196, 142, 226, 194, _
               198, 195, 148, 428, 319, 257, 204, 205, 370, 371, 247, 238, 176, 177, 360, 361, 362)
   
   For i = 0 To UBound(ary)
      With ActiveSheet.Shapes.Range("rounded Rectangle " & ary(i)).Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(255, 255, 255)
         .Transparency = 0
         .Solid
      End With
   Next i
End Sub

You can slim it down further for instance the blue values could be written as K15:Q15 & red values as G8:P8

 

[iAmPatch]

Hi @mumps

I tried to edit your code:

Private Sub Clear_Click()
    Application.ScreenUpdating = False
    Dim rng As Range
    If MsgBox("Have you printed and transferred the data to the tracker?", vbYesNo) = vbYes Then
        Call Clearcells
    Else
        For Each rng In Range("G7:N7, G8:P8, L27:W27")
            If rng = "" Then
                MsgBox ("Please fill in cell " & rng.Address(0, 0) & ".")
                rng.Select
                Application.ScreenUpdating = True
                Exit Sub
            End If
        Next rng
    End If
    Application.ScreenUpdating = True
End Sub

to look like this:

Private Sub Clear_Click()
    Application.ScreenUpdating = False
    Dim rng As Range
    If MsgBox("Have you printed and transferred the data to the tracker?", vbYesNo) = vbYes Then
        
        For Each rng In Range("G7, G8, L8")
            If rng = "" Then
                MsgBox ("Please make sure that you have fully accomplished the form.")
                rng.Select
                Application.ScreenUpdating = True
                Exit Sub
            End If
        Next rng
    Else
        Call Clearcells
    End If
    Application.ScreenUpdating = True
End Sub

but i think i messed it up. The Clearcells module is no longer being called out

@Fluff

i used your code, and i was getting an error on this particular line: For i = 0 To UBound(RAry)

oh and i'm sorry; i can't really put into english the code you've provided (though it looks quite simple)

 

[Fluff]

i used your code, and i was getting an error on this particular line: For i = 0 To UBound(RAry)

What was the error?

 

[mumps]

If you answer "No" to the prompt, the Clearcells macro should run.

 

[iAmPatch]

hi @mumps

oh sorry you're right, clicking on "No" does call out the Clearcells module.

what i was trying to do was, if the user did click on "Yes", the search macro would run (which would check for blank cells); but if there are no blank cells then the Clearcells module should still be called out.
can i just go ahead and put in the Clearcells before End If?


hi @Fluff

the error that I got was: "Run-time error '1004': Method 'Range' of object '_Global' failed"

 

[Fluff]

hi @Fluff

the error that I got was: "Run-time error '1004': Method 'Range' of object '_Global' failed"

Not on the line you showed on post#16.
That said there is a typo (I missed a ,)

RAry = Array("T3:X3", [COLOR=#0000ff]"N15:Q15"[/COLOR], "AB15", [COLOR=#0000ff]"L15:M15"[/COLOR], [COLOR=#0000ff]"K15"[/COLOR], "AB16", "T5:X5", "T7:Y7", "G7:n7", "[COLOR=#ff0000]g8:k8[/COLOR]", "[COLOR=#ff0000]l8:p8[/COLOR]", "e17:j17", _
               "k17:m17", "l27:w28", "j37:p37", "l42:m42", "r42:z46", "e45:i45","f47:y47", "i48:y48", "h53:l53", "h56:l57")