If cell is Empty!

lekkhann

New Member
Joined
Oct 12, 2019
Messages
3
Hi all,

I am trying to check for all sheets if specific call E10 is empty and it prevents user from saving.
Currently it is only working on the sheet that i am on.
It does not work on the others, i want to make it that it checks all check for a specific cell before saving.
This is currently what i have.

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Cells(10, 5).Value = "" Then
Cancel = True
MsgBox "Save cancelled"
End If
End Sub

Thanks for helping!!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,750
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Try

Code:
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
    With ws
        If .Cells(10, 5).Value = "" Then
            Cancel = True
            MsgBox "Save cancelled"
        End If
    End With
End Sub
 

lekkhann

New Member
Joined
Oct 12, 2019
Messages
3
HI,

Thanks for the prompt reply!

I have tried but it shows an error.

"Compile error, For without next" ):
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,750
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
OOps sorry....
Code:
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
    With ws
        If .Cells(10, 5).Value = "" Then
            Cancel = True
            MsgBox "Save cancelled"
        End If
    End With
next ws
End Sub
 

lekkhann

New Member
Joined
Oct 12, 2019
Messages
3
OOps sorry....
Code:
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
    With ws
        If .Cells(10, 5).Value = "" Then
            Cancel = True
            MsgBox "Save cancelled"
        End If
    End With
next ws
End Sub

omg thanks it worked like a charm!

Have been trying to solve this the entire morning!

Appreciate it!!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
If I am not mistaken, this non-looping event code should also work...
Code:
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  If Evaluate("COUNTA(" & Sheets(1).Name & ":" & Sheets(Sheets.Count).Name & "!E10" & ")") < Sheets.Count Then
    Cancel = True
    MsgBox "Save cancelled"
  End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

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