how do I block users to save the excel file if conditions not met
I created a excel template for the users to complete. However, I noticed
that the users ignore the important cells and send the files out for processing. I suppose the users save the file as well. Therefore, I would like to check if it is possibile to block the files being saved or send if the conditions are not met. Example, Name cells must be filled or Total must not be less then zero. Thanks in advance. |
how do I block users to save the excel file if conditions not met
From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the
left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) This Workbook Double click 'This WorkBook' and paste the below code to the right code pane ...The below code will check for the fields Sheet1A1 is blank and also check whether Sheet2B1 is 0..Modify to suit your requirement Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Trim(Worksheets("Sheet1").Range("A1")) = "" Then Cancel = True If Worksheets("Sheet2").Range("B1").Value = 0 Then Cancel = True If Cancel = True Then _ MsgBox "Cannot save. Mandatory fields are blank" End Sub If this post helps click Yes --------------- Jacob Skaria "Jonathan" wrote: I created a excel template for the users to complete. However, I noticed that the users ignore the important cells and send the files out for processing. I suppose the users save the file as well. Therefore, I would like to check if it is possibile to block the files being saved or send if the conditions are not met. Example, Name cells must be filled or Total must not be less then zero. Thanks in advance. |
All times are GMT +1. The time now is 09:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com