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

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

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

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

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.ColorInd ex = 8 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

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

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

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

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

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


On Sat, 13 Sep 2008 11:51:01 -0700, JohnBlack
wrote:

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

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" wrote:

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


On Sat, 13 Sep 2008 11:51:01 -0700, JohnBlack
wrote:

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

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

On Wed, 17 Sep 2008 07:43:01 -0700, JohnBlack
wrote:

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" wrote:

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


On Sat, 13 Sep 2008 11:51:01 -0700, JohnBlack
wrote:

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

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


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

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


On Sat, 13 Sep 2008 11:51:01 -0700, JohnBlack
wrote:

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

Bernie

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


Gord

On Wed, 17 Sep 2008 20:26:38 -0400, "Bernie Deitrick" <deitbe @ consumer dot
org wrote:

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


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

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


On Sat, 13 Sep 2008 11:51:01 -0700, JohnBlack
wrote:

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