VBA Express : Excel - User must fill in certain cells before they can close the workbook

Excel

User must fill in certain cells before they can close the workbook

Ease of Use

Intermediate

Version tested with

2000, 2002, 2003

Submitted by:

lucas

Description:

If specific cells do not contain data, an alert pops up telling them to complete the form. The alert tells them which cells and which sheet. It also highlights them yellow. When the form is correctly filled out, the highlights go away and the workbook is saved on close.

Discussion:

You have a form that has information that is required. This code will prevent your users from closing the book until it is completely filled out.

Code:

instructions for use

			Paste this code In the ThisWorkbook module of your workbook: 
 
Option Explicit 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Dim Start As Boolean 
    Dim Rng1 As Range, Rng3 As Range, Rng4 As Range 
     
    Dim Prompt As String, RngStr As String 
    Dim Cell As Range 
     'set your ranges here
     'Rng1 is on sheet "Group Profile" and cells B5 through B14
     'Cell F1, A range of F5 through F7 etc.  you can change these to
     'suit your needs.
    Set Rng1 = Sheets("Group Profile").Range("B5:B14,F1,F5:F7,B20:B22,B26:B31,B38:B45,B49:B52") 
    Set Rng3 = Sheets("Eligibility Guidelines").Range("F1,E5,E6,E9,E10,B7:B17,B21:B36") 
    Set Rng4 = Sheets("COBRA").Range("J2,H4,H5,J15,B4,B5,B9,B10:B13,B17:B20,B25:B28,E17:  E20") 
     'message is returned if there are blank cells
    Prompt = "Please check your data ensuring all required " & _ 
    "cells are complete." & vbCrLf & "you will not be able " & _ 
    "to close or save the workbook until the form has been filled " & _ 
    "out completely. " & vbCrLf & vbCrLf & _ 
    "The following cells are incomplete and have been highlighted yellow:" _ 
    & vbCrLf & vbCrLf 
    Start = True 
     'highlights the blank cells
    For Each Cell In Rng1 
        If Cell.Value = vbNullString Then 
            Cell.Interior.ColorIndex = 6 '** color yellow
            If Start Then RngStr = RngStr & Cell.Parent.Name & vbCrLf 
            Start = False 
            RngStr = RngStr & Cell.Address(False, False) & ", " 
        Else 
            Cell.Interior.ColorIndex = 0 '** no color
        End If 
    Next 
    If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2) 
    Start = True 
    If RngStr <> "" Then RngStr = RngStr & vbCrLf & vbCrLf 
    For Each Cell In Rng3 
        If Cell.Value = vbNullString Then 
            Cell.Interior.ColorIndex = 6 '** color yellow
            If Start Then RngStr = RngStr & Cell.Parent.Name & vbCrLf 
            Start = False 
            RngStr = RngStr & Cell.Address(False, False) & ", " 
        Else 
            Cell.Interior.ColorIndex = 0 '** no color
        End If 
    Next 
    If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2) 
    Start = True 
    If RngStr <> "" Then RngStr = RngStr & vbCrLf & vbCrLf 
    For Each Cell In Rng4 
        If Cell.Value = vbNullString Then 
            Cell.Interior.ColorIndex = 6 '** color yellow
            If Start Then RngStr = RngStr & Cell.Parent.Name & vbCrLf 
            Start = False 
            RngStr = RngStr & Cell.Address(False, False) & ", " 
        Else 
            Cell.Interior.ColorIndex = 0 '** no color
        End If 
    Next 
    If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2) 
    If RngStr <> "" Then 
        MsgBox Prompt & RngStr, vbCritical, "Incomplete Data" 
        Cancel = True 
    Else 
         'saves the changes before closing
        ThisWorkbook.Save 
        Cancel = False 
    End If 
     
    Set Rng1 = Nothing 
    Set Rng3 = Nothing 
    Set Rng4 = Nothing 
     
End Sub

How to use:

Open the Visual Basic Editor by going to tools-Macro's-Visual Basic Editor or use Alt-F11
Click on the ThisWorkbook object in the project explorer
In the module pane paste the code above.
Close the Visual Basic Editor by clicking the X in the upper right corner or go to File-Close

Test the code:

You have to have data in all of the cells listed in the code. You can locate them by just trying to close the workbook(you must first name your sheets according to the range definition in the code). When all of the cells have data in them, you will be allowed to close the workbook and all changes will be saved.

Sample File:

userMustComplete.zip 11.13KB

Approved by mdmackillop

This entry has been viewed 348 times.