Workbook_BeforeSave / Cells with required entry / Catch-22

Posted by David Megnin on July 13, 2001 10:11 AM

How do you save your worksheet once you have entered the code below? It seems to cause a catch-22.
Thanks

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("yourSheet").[a1] = "" Then
Cancel = True
MsgBox "Can't save No entry for A1"
End If

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As_ Boolean, Cancel As Boolean)
If Sheets("yourSheet").[a1] = "" Then
Cancel = True
MsgBox "Can't save No entry for A1"
End If
End Sub

Posted by Dax on July 13, 2001 12:43 PM

You need a very small change to your current code:-

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("yourSheet").[a1] = "" Then
Cancel = True
MsgBox "Can't save No entry for A1"
Else
Cancel = False
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("yourSheet").[a1] = "" Then
Cancel = True
MsgBox "Can't save No entry for A1"
Else
Cancel = False
End If
End Sub

HTH,
Dax.

Posted by lenze on July 13, 2001 12:46 PM

There's probably an easier way, but a workaround, would be to write an AutoOpen event to check cell A1 and clear contents if it is a "specific value". Then add your other code and enter the "specific value" in A1 and save. Just make sure your "Specific value" is a value which will not occur in use.