PDA

View Full Version : If cell is Empty!



lekkhann
10-11-2019, 09:23 PM
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!!

SamT
10-12-2019, 02:00 AM
Change Sheet Names
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheet1.Cells(10, 5).Value = "" Then
Cancel = True
MsgBox "Save cancelled"
ElseIf Sheet2.Cells(10, 5).Value = "" Then
Cancel = True
MsgBox "Save cancelled"
ElseIf Sheet3.Cells(10, 5).Value = "" Then
Cancel = True
MsgBox "Save cancelled"
End If
End Sub

Paul_Hossler
10-12-2019, 07:31 AM
Option Explicit


Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet

For Each ws In Worksheets
If Len(ws.Range("E10").Value) = 0 Then
Cancel = True
MsgBox "Save cancelled"
Exit Sub
End If
Next

End Sub