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

iAmPatch

Board Regular
Joined
Jan 13, 2015
Messages
86
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:
Code:
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Do you want the message "Please print and transfer data to the tracker. Thanks!" to be displayed if every cell in those ranges is empty or even if one cell in those ranges is empty?
 
Upvote 0
Hi @mumps

I'm sorry I wasn't able to edit my post... the message "Please print and transfer data to the tracker" should be changed to "Please check the form again and make sure that you have accomplished the required fields".

And to answer your question, the mesage box should appear even if one of the ranges specified is empty.

Thanks for replying to my thread.
 
Upvote 0
Try:
Code:
Private Sub Clear_Click()
    Application.ScreenUpdating = False
    Dim rng As Range
    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
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi @mumps

Thanks for this. Question though; how do I incorporate the rest of my code? I mean after it checks for blank cell ranges; and then there are no blank cell ranges it should show a message like "Congratulations" then call the module that I have which is Clearcells ()

Or is it simply just adding in the Else statement after the End If?
 
Last edited:
Upvote 0
Instead of calling the macro, we can incorporate that code into the macro I suggested. Which cells do you want to clear?
 
Upvote 0
Oh no... i dont have the copy here with me right now... but it's multiple ranges as well and some autoshapes (did i get it right?) --- some boxes that needs to be ticked

I'll post the Clearcells() module I have once I get my laptop
 
Upvote 0
OK. I'll wait for the module.
 
Upvote 0
Hi @mumps

Here you go
Code:
Sub Clearcells()
 
'clear contents
Range("T3:X3").ClearContents
Range("N15:Q15").ClearContents
Range("AB15").ClearContents
Range("L15:M15").ClearContents
Range("K15").ClearContents
Range("AB16").ClearContents
Range("T5:X5").ClearContents
Range("T7:Y7").ClearContents
Range("G7:n7").ClearContents
Range("g8:k8,l8:p8").ClearContents
Range("e17:j17").ClearContents
Range("k17:m17").ClearContents
Range("l27:w27,l28:w28").ClearContents
Range("j37:p37").ClearContents
Range("l42:m42").ClearContents
Range("r42:z46").ClearContents
Range("e45:i45").ClearContents
Range("f47:m47, n47:y47").ClearContents
Range("i48:l48, m48:o48, p48:y48").ClearContents
Range("h53:l53").ClearContents
Range("h56:l56, h57:l57").ClearContents
Range("h60:m61").ClearContents
Range("h63:m63").ClearContents
Range("n74:t75, r76:z81").ClearContents
Range("a87:d92").ClearContents
Range("e87:i92").ClearContents
Range("j87:k91").ClearContents
Range("l87:m92").ClearContents
Range("n87:s92").ClearContents
Range("t87:w90").ClearContents
Range("x87:z92").ClearContents
Range("a93:d98").ClearContents
Range("e93:i98").ClearContents
Range("j93:k97").ClearContents
Range("l93:m98").ClearContents
Range("n93:s98").ClearContents
Range("t93:w96").ClearContents
Range("x93:z98").ClearContents
Range("a99:d104").ClearContents
Range("e99:i104").ClearContents
Range("j99:k103").ClearContents
Range("l99:m104").ClearContents
Range("n99:s104").ClearContents
Range("t99:w102").ClearContents
Range("x99:z104").ClearContents
Range("a105:d110").ClearContents
Range("e105:i110").ClearContents
Range("j105:k109").ClearContents
Range("l105:m110").ClearContents
Range("n105:s110").ClearContents
Range("t105:w108").ClearContents
Range("x105:z110").ClearContents
Range("a111:d116").ClearContents
Range("e111:i116").ClearContents
Range("j111:k115").ClearContents
Range("l111:m116").ClearContents
Range("n111:s116").ClearContents
Range("t111:w114").ClearContents
Range("x111:z116").ClearContents
Range("m117:z122").ClearContents
Range("e119:i119").ClearContents
Range("p124:s124").ClearContents
Range("x124:y124").ClearContents
Range("p129:w129").ClearContents
Range("x124:y124").ClearContents
Range("o137:r137, o139:r139, o141:r141").ClearContents
Range("s151:y151, s153:y153, s155:y155, s157:y157, s159:y159, n161:y161").ClearContents
Range("b164:m164, b165:m165, b166:m166, b167:m167, n163:y167").ClearContents
Range("d172:k172, d173:k173, d174:k174, d175:k175, d176:k176, m172:r172, m173:r173, m174:r174, m175:r175, m176:r176, t172:z172, t173:z173, t174:z174, t175:z175, t176:z176").ClearContents
Range("e178:l178, e179:l179, e180:l180, e181:l181, n178:u178, n179:u179, n180:u180, n181:u181").ClearContents
Range("j184:z192").ClearContents
Range("j199:z204").ClearContents
Range("a209:j216, k209:q216, r209:z216, a217:j224, k217:q224, r217:z224").ClearContents
Range("f281:k281, p280:y284").ClearContents
Range("g289:i289, g290:i290, g291:i291, g292:i292, j289:k289, j290:k292, l289:n290, n291:n292").ClearContents
Range("p289:z292").ClearContents
Range("b295:n304, p295:z304").ClearContents
ActiveSheet.Shapes.Range(Array("AutoShape 24")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 344")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 345")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 343")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 346")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 273")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 86")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 347")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 89")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 116")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 97")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 94")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 103")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 141")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 196")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 142")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 226")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 194")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 198")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 195")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 148")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 428")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 319")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 257")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 204")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 205")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 370")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 371")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 247")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 238")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 176")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 177")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 360")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 361")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 362")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 363")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 364")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 365")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 366")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 367")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 251")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 254")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 278")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 280")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 309")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 348")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 350")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 351")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 349")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 352")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 353")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 354")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 355")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 356")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 380")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 358")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 381")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 359")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 382")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 378")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 383")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0
        .Solid
    End With
End Sub
 
Upvote 0
Just a few questions to see it we can simplify the macro. You are clearing the contents of many non-contiguous ranges. Are the values of these ranges a result of a formula? If they are a result of a formula, are there any other cells that are also a result of a formula that you don't want to clear. Do you want to format all the Rounded Rectangles on the sheet or are there some that you don't want to format?
 
Upvote 0

Forum statistics

Threads
1,214,991
Messages
6,122,628
Members
449,095
Latest member
bsb1122

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top