Data Validation: NOT exit an excel form without certain cell

  • Here's an example using the workbook beforeclose event


    <font face=Courier New&gt;<SPAN style="color:#00007F"&gt;Private</SPAN&gt; <SPAN style="color:#00007F"&gt;Sub</SPAN&gt; Workbook_BeforeClose(Cancel <SPAN style="color:#00007F"&gt;As</SPAN&gt; <SPAN style="color:#00007F"&gt;Boolean</SPAN&gt;)<br&gt;<SPAN style="color:#00007F"&gt;If</SPAN&gt; Len(Cells(1, 1) = 0) <SPAN style="color:#00007F"&gt;Then</SPAN&gt;<br&gt;MsgBox "Can<SPAN style="color:#007F00"&gt;'t Close without completing" _<br&gt;& " cell A1"</SPAN&gt;<br&gt;Cancel = <SPAN style="color:#00007F"&gt;True</SPAN&gt;<br&gt;Else: ActiveWorkbook.<SPAN style="color:#00007F"&gt;Close</SPAN&gt; <SPAN style="color:#00007F"&gt;True</SPAN&gt;<br&gt;<SPAN style="color:#00007F"&gt;End</SPAN&gt; <SPAN style="color:#00007F"&gt;If</SPAN&gt;<br&gt;<SPAN style="color:#00007F"&gt;End</SPAN&gt; <SPAN style="color:#00007F"&gt;Sub</SPAN&gt;</FONT&gt;


    If A1 is not completed, the workbook close event will be cancelled
    If A1 has a value, the workbook will close & any changes will be saved.


    Hope this helps


    Will

  • Testing a Range of cells could be done like this


    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Application.WorksheetFunction.CountA(Range("A1:E1")) <> 5 Then
    MsgBox "Can't Close without completing" _
    & " cells A1:E1"
    Cancel = True
    Else: ActiveWorkbook.Close True
    End If
    End Sub


    Will

  • Sorry Homer... had to do some work


    My code should be placed in the This_Workbook code module in the VBE


    Access this by right-clicking the XL Icon to the left of the word File on the top menu ok.


    Paste my code in this module.


    You can alter the code to make it specific to cells in, say sheet1 of the workbook if you need to....


    Will

  • Sheet specific EG


    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Len(Sheet1.Cells(1, 1)) = 0 Then
    MsgBox "Can't Close without completing" _
    & " Sheet 1, Cell A1"
    Cancel = True
    Else: ActiveWorkbook.Close True
    End If
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!