Excel

Force user to enter a value in a cell before leaving the worksheet

Ease of Use

Easy

Version tested with

2003 

Submitted by:

Ken Puls

Description:

This code forces a user to enter something in Range("A1") of Sheet1 before they can leave Sheet1. If nothing exists in the cell, the user if returned to the sheet to enter information. 

Discussion:

Sometimes you may want to prohibit a user from leaving a sheet before all required information is filled in. This code will accomplish exactly that. 

Code:

instructions for use 

			
Option Explicit 
 
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) 
     'Macro created 07/06/2005 21:29 by Ken Puls
     'Macro Purpose:  Force a user to enter a value in Range A1
     '   on Sheet1 before leaving the sheet
     
    Dim wsCheck As Worksheet 
    Dim rngCheck As Range 
     
     'Set the ranges you want to check here
    Set wsCheck = Worksheets("Sheet1") 
    Set rngCheck = wsCheck.Range("A1") 
     
     'Turn off events to avoid triggering a loop
    Application.EnableEvents = False 
     
     'Check if user is leaving Sheet1
    If Sh.Name = wsCheck.Name Then 
         
         'Check if Range A1 is empty
        If IsEmpty(rngCheck) Then 
             'If so, reactivate the sheet, select the cell, and
             'tell user they must enter a value
            wsCheck.Activate 
            rngCheck.Select 
            MsgBox "Sorry, you must enter a value in " & rngCheck.Address 
        End If 
    End If 
     
     'Restore events
    Application.EnableEvents = True 
End Sub

How to use:

  1. Copy the code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Expand the desired file on left (in bold).
  5. In the project explorer, locate the ThisWorkbook object.
  6. Double click the ThisWorkbook object.
  7. Paste code into the right pane.
  8. Change the worksheet name or range name (not the wsCheck portion) to desired values.
  9. Press Alt + Q to close the VBE.
  10. Save workbook before any other changes.
 

Test the code:

  1. Select another sheet in the workbook.
  2. When you receive the error message and are returned to sheet 1, enter something in cell A1 (if using the defaults).
  3. You should now be able to select another sheet in the workbook without a message.
 

Sample File:

ForceData.zip 7.89KB 

Approved by mdmackillop


This entry has been viewed 179 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express