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]

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")

@Fluff

amazing, it works like a charm. was wondering if you could translate in English each line of code used?

Sub Clearcells()
   Dim RAry As Variant, ary As Variant
   Dim i As Long
   
   RAry = Array("T3:X3", "N15:Q15", "AB15", "L15:M15", "K15", "AB16", "T5:X5", "T7:Y7", "G7:n7", "g8:k8", "l8:p8", "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

sorry I'm not yet that familiar with more complex codes

 

[Fluff]

HTH

Sub Clearcells()
   Dim RAry As Variant, ary As Variant
   Dim i As Long
   
   RAry = Array("T3:X3", "N15:Q15", "AB15", "L15:M15", "K15", "AB16", "T5:X5", "T7:Y7", "G7:n7", "g8:k8", "l8:p8", "e17:j17", _
               "k17:m17", "l27:w28", "j37:p37", "l42:m42", "r42:z46", "e45:i45", "f47:y47", "i48:y48", "h53:l53", "h56:l57")        ' an array of the cell address you want to clear
               
   For i = 0 To UBound(RAry)
      Range(RAry(i)).ClearContents                                                                                                  ' loops through the array clearing the relevant cells
   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)                                 ' an array holding the number from the end of the shapes names
   
   For i = 0 To UBound(ary)                                                                                                         ' loops through the array & concatenates the numbers with "rounded rectangle " to give the name of the shape
      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

 

[mumps]

Try this version:

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
        Call Clearcells
    Else
        Call Clearcells
    End If
    Application.ScreenUpdating = True
End Sub

 

[iAmPatch]

Hi @mumps

the code works flawlessly now. thank you so much for your help

Hi @Fluff

thanks for making notes of what the code means. it really helps a lot.

 

[mumps]

You are very welcome. Glad we could help.