BeforeSave Event

ctmerced

New Member
Joined
Jun 28, 2019
Messages
3
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,147
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("[COLOR="#FF0000"]Sheet1[/COLOR]").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Sheets("[COLOR="#FF0000"]Sheet1").Range("A2:A"[/COLOR] & 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:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,932
Office Version
2010
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,101,773
Messages
5,482,830
Members
407,365
Latest member
Leah Ashley

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top