Can we prevent Save option availability if it does not meet certain criteria?

Hi everyone,


I have situation where I should not allow users to click Save unless they meet certain criteria ( Eg: if excel contains Red coloured cells in any Sheet, It should not be saved).

Can I do this?

Ps: I am using Excel 2016

I have seen your code and tried to work it out. But did not end good.

I have attached my excel file here which contains one red coloured cell which is an error.

Can you please check if that code works on my excels and recommend me procedure ?

Well, there are red and red. when I used "vbRed" this is the strongest red color i.e. = 3 so whatever shade of red you are using you must find the colour code for that

I've modified your file so you can save it, them open it and run the normal macro "MakeRed". This will set the range A102:M102 on sheet "Compare" to the "vbRed" color.

After doing so you can now try to save this file and see what happens when color is set to match the color specified in macro.

To find what code gives what color you could run this macro in a workbook:

Please Login or Register  to view this content.

Alf

doing a quick check on the color you used in sheet "Compare" range A102:M102 you need to change the line

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

in order to get the macro to recognize the color you used.

Alf

Had another go at it on my second PC and now the line

Please Login or Register  to view this content.

don't work so after testing a bit more on my second PC I found this line work

Please Login or Register  to view this content.

It looks like how mixed colors are defined may depend on the PC??? Sounds strange so my best advice to you is to record a macro when you select the color and then take the color definition from that marco unless you use a clearly defined color like for instance "vbRed"

Alf

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wks As Worksheet
Dim cell As Range

For Each wks In ActiveWorkbook.Worksheets

For Each cell In wks.UsedRange
If cell.Interior.Color = RGB(250,128,114) Then
MsgBox ("Error in Data, workbook will no be saved!")
wks.Activate
Cancel = True
Exit Sub
End If
Next

Next

End Sub





Thank you so much for your kind words , the above code worked fine.
I changed the color code in the code.

Can you extend your help once more please ?

Now, I have attached the same excel below, in that there is a sheet named ddd .

In that, cells A1A2, B1B2 will always be in RGB(250,128,114).

I want to exclude A1B1 always .(with text or without text)

I want to include A2B2 if there is text then say "indefinite error" . OR ELSE exclude.

The same with A3B3, A4B4, A5B5 and so on,if there is text then
say "indefinite error" . OR ELSE exclude.(incase text is there, the cell will be in RGB(250,128,114)).


How can we put this in the code? Is it possible?

Hi David

I suggested how to get the interior.color

You certainly did but when I posted I only saw mine postings. This is probably caused by the fact that all postings are adjusted to GMT when they are posted so when persons are in different time zones .....

This is not the first time something like this has happened i.e. when I post I'm the only one that have posted in the thread but when I check the next morning somebody has made an earlier posting than mine.

Alf

Hi akipaul

I'll have a look at new request and see if I come up with something useful.

Alf

At the moment I'm not sure I understand you new request.

You asked for and got a macro that stops the saving of a file if a cell or cells on sheet or on different sheets. From your uploaded file the stop trigger RGB code is (250, 128, 114)

Now you ask that on sheet ddd that if A2B2 contains text "Infinite error" than these cells should be colored using RGB(250, 128, 114) and all other cells that contain this text.

Should both A2 and B2 contain this text or if only one cell A2 or B2 contain "Infinite error" still both cells shall be colored RGB(250, 128, 114)

The fastest way to color cells in A2:BXX" range would to set an autofilter to find all the cells on sheet ddd that match the "Infinite error" criteria, color these using RGB(250, 128, 114) and the remove the autofilter.

But do you still test if any cell has the RGB(250, 128, 114) color because this means you can't save the file. Also you do say that A1A2 and B1B2 always have the RGB(250, 128, 114) color setting so I'm really puzzled.

Alf