Disable File Save And Highlight Empty Cells

[hobbiton73]

Hi, firstly my apologies because this may not even be possible, but I have little VB knowledge, so I just thought I'd ask.

I'm using the code below taken from here http://chandoo.org/forums/topic/if-excel-cell-is-blank
to send a message to the user which asks them if they want to save the file although the worksheet may have empty cells.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Worksheet As Worksheet
Dim RequiredCells As Range
Dim EmptyCell As Range
Dim EmptyCells As Variant

Set Worksheet = Worksheets("Combined")
Set RequiredCells = Worksheet.Range("Input")

EmptyCells = Null

On Error Resume Next ' SpecialCells will generate an error if there are no blanks.
For Each EmptyCell In RequiredCells.SpecialCells(xlCellTypeBlanks).Cells
If IsNull(EmptyCells) Then ' FIRST empty cell found
EmptyCell.Select ' Move selection to the empty cell.
EmptyCell.Show ' Make sure it's in the window
End If
' Build a list of empty cells for the MsgBox
EmptyCells = (EmptyCells + " and ") & Replace(EmptyCell.AddressLocal, "$", "")
Next
On Error GoTo 0

If Not IsNull(EmptyCells) Then ' Empty cells found
' Cancel Save operation unless user clicks "YES" button
Cancel = MsgBox(Title:="Required data missing.", _
Prompt:="A value is required in " & EmptyCells & _
" before the workbook should be saved." & vbCrLf & _
vbCrLf & _
"Save anyway?", _
Buttons:=vbYesNo + vbQuestion + vbDefaultButton2 + vbMsgBoxSetForeground _
) = vbNo
End If

Worksheet.Activate

Set EmptyCell = Nothing
Set RequiredCells = Nothing
Set Worksheet = Nothing
End Sub

The problem I have is two-fold.

Could someone possibly please offer some guidance on how I may go about changing the code:
So that the user cannot save the file until all the necessary cells are filled, and

Because I'm using a range of approx 500 cells, rather than an error message listing every cell that is empty, is it possible to highlight these cells with fill colour 10 on the worksheet and have a generic message to say 'The file cannot be saved until all necessary fields are completed.'

As, I said, I'm not sure whether this is even possible, but I really would appreciate any guidance given.

Many thanks and kind regards

[GTO]

I'm not sure if you really want to insist that the cells are filled, or make the user override to save if there are empty cells. Picking the latter, maybe something like:
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wks As Worksheet
Dim RequiredCells As Range
Dim Cell As Range
Dim MissingCells As Range
Dim bolMissingData As Boolean

Set wks = Worksheets("Combined")
Set RequiredCells = wks.Range("Input")

For Each Cell In RequiredCells
If Cell.Value = vbNullString Then
bolMissingData = True
If MissingCells Is Nothing Then
Set MissingCells = Cell
Else
Set MissingCells = Application.Union(Cell, MissingCells)
End If
End If
Next

If bolMissingData Then
If Not MsgBox( _
"You have not entered required info in cells: " & _
MissingCells.Address(0, 0, xlA1, 0) & _
vbCrLf & vbCrLf & _
"Press <Yes> to override and save, or <No> to cancel the save " & _
"and correct the sheet.", _
vbYesNo Or vbInformation, _
vbNullString) = vbYes Then

Cancel = True
wks.Select
MissingCells.Select
End If
End If
End Sub
hope that helps,

Mark

[hobbiton73]

Hi @GTO, thnak you very much for taking the time to reply to my post and for the solution.

I took on board what you've said and made some changes, in that the cell shading is done via 'Conditional Formatting', but I would like to keep the functionality which disables the file save.

I have found following simple script which I think will serve my purpose, but I've found it difficult in finding how to run the script looking in my range for all cells within that range of fill colour 3.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If IsEmpty(['Sheet name'!C3]) Then
Cancel = True
Msgbox "A value is required in cell C3 before the workbook can be saved."
End If
End Sub

I just wondered whether you could possibly provide a little guidance please how I can adapt the code to search my range for all the cells with the fill colour 10?

Many thanks and regards

[GTO]

...The problem I have is two-fold.

Could someone possibly please offer some guidance on how I may go about changing the code:

So that the user cannot save the file until all the necessary cells are filled, and
Because I'm using a range of approx 500 cells, rather than an error message listing every cell that is empty, is it possible to highlight these cells with fill colour 10 on the worksheet and have a generic message to say 'The file cannot be saved until all necessary fields are completed.'

Hi @GTO, thnak you very much for taking the time to reply to my post and for the solution.

I took on board what you've said and made some changes, in that the cell shading is done via 'Conditional Formatting', but I would like to keep the functionality which disables the file save.

I have found following simple script which I think will serve my purpose, but I've found it difficult in finding how to run the script looking in my range for all cells within that range of fill colour 3.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If IsEmpty(['Sheet name'!C3]) Then
Cancel = True
Msgbox "A value is required in cell C3 before the workbook can be saved."
End If
End Sub

I just wondered whether you could possibly provide a little guidance please how I can adapt the code to search my range for all the cells with the fill colour 10?

Many thanks and regards

:p ...ouch, my poor brain.

I am afraaid that I am not following. I understand (or at least hopefully do) that you have changed to using conditional formatting for marking the cells that are empty. IMO, this is a good choice, as changing the cells' actual color leaves one the issue of changing it back once the user plunks somethiing into the cell.

The bits in red though, have me quite lost. Here's a best guess, presuming the conditional formatting "colors" the cell to ColorIndex 10 (if the cell is empty)...

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wks As Worksheet
Dim RequiredCells As Range
Dim Cell As Range
Dim MissingCells As Range
Dim bolMissingData As Boolean

'// You could combine these, to just return the range; but for clarity, we'll //
'// keep everything on its own for the moment. //
Set wks = Worksheets("Combined")
Set RequiredCells = wks.Range("Input")

'// Run (loop) through all the cells in our range of interest,... //
For Each Cell In RequiredCells
'// ...testing to see if the cell meets the same condition(s) that our //
'// conditional formatting is testing for. We are not looking to see what //
'// the cells' colorindex is, as conditional formatting doesn't actually //
'// change the colorindex; think of it as more a temporary colored bit of //
'// glass you hold over the cell to make it "appear" whatever color, vs. //
'// changing the cell's color, which would be like applying a fresh coat of //
'// paint to the cell, ie, and actual change to the cell. //
If Cell.Value = vbNullString Then
'// The first cell found that is empty, sets our flag to true. //
bolMissingData = True
'// This is to test if our range MissingCells has been "created" yet. //
'// If not (Is Nothing), then we'll "create" and set a reference to the //
'// first empty cell. On the other hand... //
If MissingCells Is Nothing Then
Set MissingCells = Cell
Else
'// ...if MissingCells has been Set, then we will redefine it, adding //
'// each cell that we find empty. //
Set MissingCells = Application.Union(Cell, MissingCells)
End If
End If
Next

'// If our flag is set to TRUE, we ran into at least one empty cell, so tell //
'// the user and Cancel the Save. //
If bolMissingData Then
MsgBox "Save Cancelled - You have not entered required info in cells: " & _
MissingCells.Address(0, 0, xlA1, 0), _
vbOKOnly Or vbInformation, _
vbNullString

Cancel = True
wks.Select
'Optional
'MissingCells.Select
End If
End Sub

Hope that helps,

Mark

[hobbiton73]

Hi @GTO, I'm sorry I've caused you so many problems, but I started to look at my requirements and what you've said throughout this exchange.

One of your earlier comments, was that it may be better to look at the value rather than the color, and I think that this is what I'm going to go away and try to do.

I do want to thank you very much for all your time, trouble and patience.

Kind regards

[GTO]

No trouble at all, my teasing was certainly meant in a friendly manner :-) It is always a learning experience, and we all have to figure out the best way to 'skin a cat' every now and then.