Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check for Non Blank and Blank Cells Before Save
Does anyone know the VBA to only allow A1 is a non blank cell and B1 is a blank cell before allowing the save? Thanks Igbert |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check for Non Blank and Blank Cells Before Save
Right click the XL icon in the top left hand corner of the XL screen and
select View code. That will take you into the ThisWorkbook code module within the VBE. paste the following... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) With Sheets("Sheet1") If Not (.Range("A1").Value < "" And .Range("B1").Value = "") Then MsgBox "Can't save file" Cancel = True End If End With End Sub Note that when you do things like this you tend to frustrate your users. They just want to save the file. Thye don't know what is supposed to be in A1 so they just put in anything to make it work. Or they 'need' to have something in B1 and... or they are sure they saved the file but now it is not there (because they did not read the warning that the file was not being saved)... or... -- HTH... Jim Thomlinson "igbert" wrote: Does anyone know the VBA to only allow A1 is a non blank cell and B1 is a blank cell before allowing the save? Thanks Igbert |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check for Non Blank and Blank Cells Before Save
Hi Jim,
Thanks for the codes. They works. I wonder if I could check for mandatiory entry of multiple cells in E6, J8, N8, Q6, Q7, Q8, and Cell R7 must be left blank before allowing the working sheet to save. I prefer to have the prompt message for the mandatory cells. Here are my codes. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Mandatory fill in Cells before allow to save Dim cell As Range For Each cell In Sheets("Account Profile").Range("E6,J8,N8,Q6,Q7,Q8") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address & " of the Account Profile Worksheet." Application.Goto cell Cancel = True Exit For End If Next cell End Sub Many thanks. Igbert "Jim Thomlinson" wrote: Right click the XL icon in the top left hand corner of the XL screen and select View code. That will take you into the ThisWorkbook code module within the VBE. paste the following... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) With Sheets("Sheet1") If Not (.Range("A1").Value < "" And .Range("B1").Value = "") Then MsgBox "Can't save file" Cancel = True End If End With End Sub Note that when you do things like this you tend to frustrate your users. They just want to save the file. Thye don't know what is supposed to be in A1 so they just put in anything to make it work. Or they 'need' to have something in B1 and... or they are sure they saved the file but now it is not there (because they did not read the warning that the file was not being saved)... or... -- HTH... Jim Thomlinson "igbert" wrote: Does anyone know the VBA to only allow A1 is a non blank cell and B1 is a blank cell before allowing the save? Thanks Igbert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to check for blank cells with formula | Excel Discussion (Misc queries) | |||
Using Vlookup and IF statements to check for blank cells | Excel Worksheet Functions | |||
check a row ignore blank cells print only those with text | Excel Discussion (Misc queries) | |||
check for two blank cells before populating a 3rd. | Excel Worksheet Functions | |||
Save As .XML, not exporting blank cells | Excel Discussion (Misc queries) |