Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I disable "cutting cells" and "drag and drop "in excel ? | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
"Save" and "Save As" options greyed out - "Save as Webpage" option | Excel Discussion (Misc queries) | |||
Disable "save" option on close. | Excel Discussion (Misc queries) | |||
How can I get "File Close" to prompt me to "Save Changes" ?? | Excel Programming |