If cell is Empty!

[lekkhann]

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!!

 

[Michael M]

Try

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]

HI,

Thanks for the prompt reply!

I have tried but it shows an error.

"Compile error, For without next" ):

 

[Michael M]

OOps sorry....

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]

OOps sorry....

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]

If I am not mistaken, this non-looping event code should also work...

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