BeforeSave Event
Results 1 to 6 of 6

Thread: BeforeSave Event

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default BeforeSave Event

    Hello-
    New to VB scripting. Here's what I'm trying do.
    1) Prevent the user from saving the spreadsheet, unless the value within a column range is either "YES" or "NO". I'm providing the user with a default value on each of the cells that they must change. If the user tries to save the spreadsheet without changing the value to either "YES" or "NO", then I would like to display a message and return them to the spreadsheet.

    Any help will be greatly appreciated.

    Carlos

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

    Default Re: BeforeSave Event

    Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Change the sheet name and range (in red) to suit your needs. Close the window to return to your sheet.
    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Application.ScreenUpdating = False
        Dim LastRow As Long, rng As Range
        LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For Each rng In Sheets("Sheet1").Range("A2:A" & LastRow)
            If rng <> "YES" And rng <> "NO" Then
                MsgBox ("Cell " & rng.Address(0, 0) & " must be 'YES' or 'NO'.")
                Cancel = True
            End If
        Next rng
        Application.ScreenUpdating = True
    End Sub
    Last edited by mumps; Jun 28th, 2019 at 03:56 PM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,287
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    8 Thread(s)

    Default Re: BeforeSave Event

    You can greatly improve your chances of getting a solution if you provide some detail about your workbook.
    On what sheet (sheet name) or sheets does the column range you want to monitor exist? What is the exact address of the range?
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: BeforeSave Event

    Thank you very much! This worked great!

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: BeforeSave Event

    This works great! Thank you for taking the time!

  6. #6
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

    Default Re: BeforeSave Event

    You are very welcome.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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