Check for Non Blank and Blank Cells Before Save

[igbert]

Does anyone know the VBA to only allow A1 is a non blank cell and B1 is a
blank cell before allowing the save?

Thanks


Igbert

 

[Jim Thomlinson]

Right click the XL icon in the top left hand corner of the XL screen and
select View code. That will take you into the ThisWorkbook code module within
the VBE. paste the following...


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets("Sheet1")
If Not (.Range("A1").Value <> "" And .Range("B1").Value = "") Then
MsgBox "Can't save file"
Cancel = True
End If
End With
End Sub

Note that when you do things like this you tend to frustrate your users.
They just want to save the file. Thye don't know what is supposed to be in A1
so they just put in anything to make it work. Or they 'need' to have
something in B1 and... or they are sure they saved the file but now it is not
there (because they did not read the warning that the file was not being
saved)... or...

 

[igbert]

Hi Jim,

Thanks for the codes. They works.

I wonder if I could check for mandatiory entry of multiple cells in E6, J8,
N8, Q6, Q7, Q8, and Cell R7 must be left blank before allowing the working
sheet to save.
I prefer to have the prompt message for the mandatory cells.


Here are my codes.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Mandatory fill in Cells before allow to save

Dim cell As Range
For Each cell In Sheets("Account Profile").Range("E6,J8,N8,Q6,Q7,Q8")
If IsEmpty(cell.Value) Then
MsgBox "You must fill in cell " & cell.Address & " of the Account
Profile Worksheet."
Application.Goto cell
Cancel = True
Exit For
End If
Next cell

End Sub


Many thanks.

Igbert