Disable "Save" &/or "Close"
 

Currently I have an excel file that goes to users to enter data which I
then collate.

There is a hidden column at the right of all the data entry spaces that
checks for invalid responses or blank cells and highlights them.
Unfortunately I still get files back from users with some empty cells.

I spend hours opening these files, realising they're useless and have
to resend them to the original user.

Is there any way, then, of being even more obstructive than Iam
currently, so that users cannot even save the file or close Excel while
there remain gaps in there data?

Thanks in advance,
Ed



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Disable "Save" &/or "Close"
 

Ed

You could put some code in the ThisWorkbook code module. I have used the
Workbook_BeforeClose() Event. To enter this code, right click on the Excel
Icon in the top left of the workbook and select view code. It goes in the
resultant pane.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myCell As Range
For Each myCell In Worksheets("Sheet1").Range("A1:A4")
If IsEmpty(myCell.Value) Then
MsgBox "You have not filled in all cells, you cannot close the workbook"
Cancel = True
Exit Sub
End If
Next myCell
End Sub

It looks at the range A1:A4 in sheet1 and if there are any blanks it
displays a message box and cancels the close.

Iteration of the range may not be the best way to handle large areas of
data, but if you have this post back and we could use SpecialCells or
similar. If you want to stick with iteration of the range and it is a lot
of data, you may wish to put another message box at the start of the code,
warning users the workbook is being checked for blanks, or use a status bar
message.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"universal" wrote in message
...
Currently I have an excel file that goes to users to enter data which I
then collate.

There is a hidden column at the right of all the data entry spaces that
checks for invalid responses or blank cells and highlights them.
Unfortunately I still get files back from users with some empty cells.

I spend hours opening these files, realising they're useless and have
to resend them to the original user.

Is there any way, then, of being even more obstructive than Iam
currently, so that users cannot even save the file or close Excel while
there remain gaps in there data?

Thanks in advance,
Ed



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Disable "Save" &/or "Close"
 

Thanks Nick,

That works a treat!

Any ideas how I might handle this in reverse? The users return data in
varying amounts (some have hundreds of rows, while some may only have a
couple).

Maybe the macro needs to determine how far down (how many rows) the
spreadsheet data goes. Or to look towards the data validation columns
already on the right, so that if that range is TRUE/1, the msgbox is
displayed.

Maybe Im looking at this the wrong way though!!

Many thanks,

Ed



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Disable "Save" &/or "Close"
 

Ed

Much depends on how much editing has been done by way of ddeleting rows, as
XL does not reset it's UsedRange setting easily.

You could however put, in place of the fixed range

For each mycell in Range("A1:A4")

For Each myCell in Worksheets("Sheet1").UsedRange

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

universal wrote:
Thanks Nick,

That works a treat!

Any ideas how I might handle this in reverse? The users return data
in varying amounts (some have hundreds of rows, while some may only
have a couple).

Maybe the macro needs to determine how far down (how many rows) the
spreadsheet data goes. Or to look towards the data validation columns
already on the right, so that if that range is TRUE/1, the msgbox is
displayed.

Maybe Im looking at this the wrong way though!!

Many thanks,

Ed



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS