Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Before Save (XP; SP3)

    How does one prevent the file from being save if certain cells do not have data entered in them. In my example all cells in range B4:B32 should have something in them. If they don't a message box will appear. I have added a line of code to end the routine but the code processes the save anyway. I understand why it is completing the save [BeforeSave].


    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    For Each oCell In Range("B4:B32")
    If oCell <> "" Then
    'Do Nothing
    Else
    MsgBox "Missing Data: " & oCell.Address
    End If
    Next oCell
    MsgBox "File Not Saved. Please Resolve Missing Data"
    End
    End Sub


    Your assistance is appreciated,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Before Save (XP; SP3)

    Insert a line

    Cancel = True

    to prevent the workbook from being saved.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Before Save (XP; SP3)

    Hans,

    Thank you,
    John

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Before Save (XP; SP3)

    Try this:

    <code>
    Option Explicit

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim oCell As Range
    Dim strMissing As String
    With Worksheets("Sheet1")
    For Each oCell In Range("B4:B32")
    If oCell.Value = "" Then
    strMissing = strMissing & ", " & oCell.Address(False, False)
    End If
    Next oCell
    If strMissing <> "" Then
    strMissing = Right(strMissing, Len(strMissing) - 2)
    MsgBox "Cells " & strMissing & " must contain data."
    Cancel = True
    End If
    End With
    End Sub
    </code>
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •