Prevent users from saving if cells are blank

[JohnBlack]

Dear all, I'm having a problem with a macro I wrote. I have an excel file
which needs to be filled out. I have used conditional formatting so that when
for example C4 gets filled out, Cell D4 gets a blue color. Becasue users keep
forgetting to fill out this blue colorded cell I want to prevent them from
saving it, until they fill it out.
But for some reason my code doesn't work at all. Saving is still possible
when leaving the cells blank

See code below

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

If ActiveWorkbook.Range("D4").Interior.Color = RGB(0, 255, 255) And
ActiveWorkbook.Range("D4") = "" Then
MsgBox ("Please fill out the Blue highlighted cells.")
Cancel = True

ElseIf ActiveWorkbook.Range("D6").Interior.Color = RGB(0, 255, 255) And
ActiveWorkbook.Range("D6") = "" Then
MsgBox ("Please fill out the Blue highlighted cells.")
Cancel = True

End If

End Sub

Can anyone help me

 

[Bernie Deitrick]

Activeworkbook doesn't have a range.

Use

If Acticeworkbook.Worksheets("Sheetname).Range("D4").Value = "" OR _
Acticeworkbook.Worksheets("Sheetname).Range("D6").Value = "" Then
MsgBox ("Please fill out Cells D4 and/or D6 - highlighted in blue.")
Cancel = True
End If

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Oops - I forgot a " Worksheets("Sheetname") in both places....

HTH,
Bernie
MS Excel MVP

 

[JohnBlack]

Hi Bernie i changed my code and added a range, I left out the rest of your
solution as it disregards the fact that it should only show the message when
the cell is highlighted blue and empty. The cell doesn't always have to be
filled out. Only when it's highlighted blue. See new code below

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

If Worksheets("Sheet1").Range("D4").Interior.Color = RGB(0, 255, 255)
And Worksheets("Sheet1").Range("D4") = "" Then
MsgBox ("Please fill out the Blue highlighted cells.")
Cancel = True

ElseIf Worksheets("Sheet1").Range("D6").Interior.Color = RGB(0, 255,
255) And Worksheets("Sheet1").Range("D6") = "" Then
MsgBox ("Please fill out the Blue highlighted cells.")
Cancel = True

End If

End Sub

But I can still save the file. The macro still isn't working

 

[Bernie Deitrick]

I would use ColorIndex instead of Color - Excel doesn't actually use all the bajillions of colors
that can be created with Color - it effectively bins the colors, so try

IF Worksheets("Sheet1").Range("D4").Interior.ColorIndex = 8 And _
Worksheets("Sheet1").Range("D4").Value = "" Then

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Or use this, which is the basis of your CF:

Worksheets("Sheet1").Range("C4").Value <> "" And Worksheets("Sheet1").Range("D4")..Value = "" Then

 

[JohnBlack]

Changing Interior.Color to Interior.ColorIndex has no impact. The macro still
won't prevent me from saving.

 

[Bernie Deitrick]

Then you may not have the code in the Thisworkbook's codemodule, or you have turned enableevents to
false, or macros are not enabled, or your seemingly empty cell isn't really empty, or your
colorindex wasn't really 8, or....

Try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox ("I'm being saved.")
End Sub

Do you get the message when you save the workbook?

Then try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets("Sheet1").Range("C4").Value <> "" Then
MsgBox ("I'm being saved, and C4 is not blank.")
Else
MsgBox ("I'm being saved, and C4 is blank.")
End If
End Sub

and save the workbook.

Then try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets("Sheet1").Range("C4").Value <> "" And Worksheets("Sheet1").Range("D4").Value
= "" Then
MsgBox ("I'm being saved, and C4 is not blank, and D4 is blank.")
Else
MsgBox ("I'm being saved, and C4 is blank, or D4 is not blank.")
End If
End Sub
etc. etc.

HTH,
Bernie
MS Excel MVP

 

[JohnBlack]

Hi Berniem thanks for your reply. I tried the first macro

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox ("I'm being saved.")
End Sub

and it wont even show me a msg box. I really have no idea whats wrong. This
thing is making me crazy. I've uploaded the excel file. Could you be so kind
and inspect it for me. Heres the URL

http://www.zshare.net/download/18717139ed5d1576/

Thanks in advance

 

[Gord Dibben]

Re-read Bernie's last post.

This is not a macro. It is event code.

You do not have your code in Thisworkbook module as he guessed.

Move it from Module1 into Thisworkbook module.


Gord Dibben MS Excel MVP

 

[JohnBlack]

Thanx alot Gord, I missed Bernie' s reference to code in Thisworkbook. When
using his examples I now do get the msg boxes. The problem is my code still
doesn't work. This proves my suspicion that the color given by the
conditional formatting isn't seen by Excel as a real color. When I for
example right click the cell, and choose format cells, the properties windows
doesn't show up.

Bernie, Gord thanks for all your help.

 

[Gord Dibben]

Yes..............Excel does not recognize the index of CF colored cells.

I did not read your original post where you mentioned CF cells.

Would have saved you and Bernie a lot of time and effort If I had picked
that up.

See Chip Pearson's site for more on CF and how to deal with CF

http://www.cpearson.com/excel/CFColors.htm


Gord

 

[Bernie Deitrick]

John and Gord:

That is why I posted this a few posts back:

Or use this, which is the basis of your CF:

Worksheets("Sheet1").Range("C4").Value <> "" And
Worksheets("Sheet1").Range("D4")..Value = "" Then


Bernie

 

[Gord Dibben]

Bernie

Was not following along too closely and missed all of that too<g>


Gord