New Excel Forum

This forum has been moved to TeachExcel.

Ask all future questions in the new excel forum.

ExcelKey

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

Postby Sisyphus » Fri Aug 30, 2013 11:00 pm

This code prompts the user to fill specified cells in a worksheet before saving.
Code: Select all
Option Explicit

Private 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 i
End 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! :D

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

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 2 guests