# Premium Excel Course Now Available!

## Prompt For Data Entry Before Saving

Free Excel Macros
Forum rules
This forum is closed.

All new posts should be made at our new Excel Forum at TeachExcel.com.

### Prompt For Data Entry Before Saving

This code prompts the user to fill specified cells in a worksheet before saving.
Code: Select all
`Option ExplicitPrivate Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)    Const FillCells As String = "B3:B6,D:8,E3:E4"    Const SheetName As String = "Sheet1"        Dim Ws As Worksheet    Dim Msg As String    Dim Sp() As String    Dim Rng As Range    Dim i As Long    Dim R As Long        Set Ws = Sheets(SheetName)        Sp = Split(FillCells, ",")    For i = LBound(Sp) To UBound(Sp)        Set Rng = Ws.Range(Sp(i))        With Rng            For R = 1 To .Cells.Count                If Len(Trim(.Cells(R).Value)) = 0 Then                    Msg = "Required data in cell " & _                          .Cells(R).Address(0, 0) & _                          " have not been supplied." & vbCr & _                          "Do you want to save the workbook anyway?" _                          & vbCr & vbCr & _                          "Press 'No' to complete data entry first."                    Cancel = Not MsgBox(Msg, _                             vbQuestion + vbYesNo + vbDefaultButton2, _                             "Missing data") = vbYes                    .Cells(R).Select                    Exit Sub                End If            Next R        End With    Next iEnd Sub`
The two constants at the top of the code can be customized to meet your requirements:-
Const FillCells As String = "B3:B6,D:8,E3:E4"
Const SheetName As String = "Sheet1"
Set the name of your own worksheet and specify the ranges and/or cells that must be filled separated by the standard list separator used in your system (it is a comma for me). Install the above code in the ThisWorkbook code sheet of your VBA project.

When the user tries to save the workbook by any method the program will check the specified ranges. If any of the cells is found blank a message will offer the choice of going back to fill the cell or continue saving. It is possible to do both.

The attached workbook has the same code ready installed for testing.
You do not have the required permissions to view the files attached to this post.
Have a great day!

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator

Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010