Check through populated cells to Cancel Save

vvsp1995

New Member
Joined
Apr 23, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hello I've created the below Macro in order to check that if column A is populated then column C must also be populated. However, I am only able to do that for a single row.

How can I enhance this macro so that excel will check all the instances where a row in Column A is populated and check against Column C to ensure that is also a populated cell? I assume I need a for loop, but haven't come across a thread that targets my specific issue. Thanks in Advance!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Range("A2").Value <> "" And Range("C2") = "" Then
Cancel = True
MsgBox "Save cancelled"
End If
End Sub
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,374
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

I am assuming that your workbook only has one sheet, as you do not have any sheet references in your code (so it will automatically just run against the active sheet).
Try this:
VBA Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim lr As Long
    Dim r As Long
    
'   Find last entry in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows
    For r = 2 To lr
'       Check values of columns A and C
        If Cells(r, "A").Value <> "" And Cells(r, "C") = "" Then
            Cancel = True
            MsgBox "Save cancelled"
            Exit For
        End If
    Next r
    
End Sub
 

Forum statistics

Threads
1,136,969
Messages
5,678,890
Members
419,787
Latest member
juanam

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
Top