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

"Bernie Deitrick" wrote:

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" wrote in message
...
Changing Interior.Color to Interior.ColorIndex has no impact. The macro still
won't prevent me from saving.

"Bernie Deitrick" wrote:

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

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

--
HTH,
Bernie
MS Excel MVP


"JohnBlack" wrote in message
...
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" wrote:

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

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"JohnBlack" wrote in message
...
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