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

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
HI,

Thanks for the prompt reply!

I have tried but it shows an error.

"Compile error, For without next" ):
 
Upvote 0
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
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

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