How can I disable the save function depending on one cell?
 

Hi! If anybody could help me with this I'd be really, really grateful!

I am designing a new tracking document, and this will be used by several
people. In case anybody makes a mistake, I have entered a formula to one cell
so that if this particular mistake is made (leaving a cell blank within a
particular range of cells), the warning cell will shade red and the text will
pop up reading "Fatal Error". Hopefully the user will notice, but if they
didn't, they would currently still be able to save the worksheet with this
error present.

I am trying to find a way to disable the save option if this particular cell
does show the text "Fatal Error". They would also need to be informed that it
cannot save (otherwise they may think it HAS saved and lose all the data
inputted). It would be even better if it was possible to show a dialog box,
where I could choose the text shown, reminding them of what the exact error
is, so that they can rectify it and then save the file.

If there is a non-macro way that would be ideal, but if not I can try VBA. I
am very new to this but I'm pretty sure I could follow instructions on it.

The "warning cell" is cell BD1 on a sheet named "Individual Support". (Sheet
4)

Does anybody have any ideas? I'd really appreciate the help!
Neil

How can I disable the save function depending on one cell?
 

Hi Neil,

I cannot think of a non macro way.

Using a macro you could use the Workbook_BeforeSave event in the
ThisWorkbook object.
You check the contents of the cell programatically as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If CStr(Sheet4.Range("BD1").Value) = "Fatal Error" Then
MsgBox "cannot save. cancelling op"
Cancel = True
End If
End Sub

Regards,
Jayant

How can I disable the save function depending on one cell?
 

My apologies Jayant - it's working now. I'm afraid that it was due to my
inexperience with macros. I had had to delete a different cell in row 1, and
so the "warning cell" that shows the "Fatal Error" message had moved left to
BC1 instead of BD1. Not knowing about Visual Basic, I had thought that the
macro would track the cell observed and update the code, in the way that
functions automatically update the references when cells are moved within a
worksheet,.... and I now realise that this is not the case. The macro still
operated on BD1, which was now blank. I shall be more careful in future to
keep the cells where they are!

Again, my apologies, and thankyou very much for your help,
Neil.



"Neil Goldwasser" wrote:

Thanks for your help Jayant. But unfortunately I can't get it to work - the
SaveAs dialog box still pops up. What might I be doing wrong? Is it because
the "Fatal Error" warning is the result of a formula rather than being typed
in?

Many thanks, Neil


"jjk" wrote:

Hi Neil,

I cannot think of a non macro way.

Using a macro you could use the Workbook_BeforeSave event in the
ThisWorkbook object.
You check the contents of the cell programatically as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If CStr(Sheet4.Range("BD1").Value) = "Fatal Error" Then
MsgBox "cannot save. cancelling op"
Cancel = True
End If
End Sub

Regards,
Jayant

How can I disable the save function depending on one cell?
 

HI Neil,

Instead of hard cell references you could try naming them.
On the menu bar goto: Insert-Name-Define
You could define a name for the error cell. Lets say 'ErrCell'.
You could then use the reference of this name in your code.
Sheet4.Range("ErrCell").Value

Regards,
Jayant

How can I disable the save function depending on one cell?
 

Fantastic! I'll remember that next time I'm using a macro. Still trying to
teach myself how to use macros at the moment, but I'm getting there. Your
advice is helping that progress a great deal. Thankyou!

Neil



"jjk" wrote:

HI Neil,

Instead of hard cell references you could try naming them.
On the menu bar goto: Insert-Name-Define
You could define a name for the error cell. Lets say 'ErrCell'.
You could then use the reference of this name in your code.
Sheet4.Range("ErrCell").Value

Regards,
Jayant