Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Worksheet When Workbook Is Saved
Hi, could someone tell me how to do this in vba ?
When a user tries to save the workbook, for Sheet1 if any cell in column A (A3 until the last row) equals "3" or column B (B3 until the last row) equals "Calculus" then prevent the workbook from being saved & prompt a msgbox if the corresponding cell in column C , F & H is blank. Thanks ! Rgds, Clare |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Worksheet When Workbook Is Saved
So, all of those conditions must be met, correct? If so, place this
in the ThisWorkbook module of the applicable workbook. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Dim lRow As Long With Sheets("Sheet1") lRow = .Cells.Find(what:="*", _ After:=.Cells(1, 1), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row For i = 3 To lRow If .Cells(i, 1).Text = "3" Or _ .Cells(i, 2).Text = "Calculus" Then If IsEmpty(.Cells(i, 3)) And _ IsEmpty(.Cells(i, 6)) And _ IsEmpty(.Cells(i, 8)) Then Cancel = True MsgBox "Your message here" Exit For End If End If Next i End With End Sub Clare wrote: Hi, could someone tell me how to do this in vba ? When a user tries to save the workbook, for Sheet1 if any cell in column A (A3 until the last row) equals "3" or column B (B3 until the last row) equals "Calculus" then prevent the workbook from being saved & prompt a msgbox if the corresponding cell in column C , F & H is blank. Thanks ! Rgds, Clare |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Worksheet When Workbook Is Saved
Sorry for late reply. I tested out the code & it worked well.
Some more questions on this code: 1) If I don't want to prevent the user from saving, but just prompt them which row(s) that is not fulfilling the criteria, allow them to click "OK", then prompt them to give them a choice to save the workbook or don't save and continue working with the workbook.... how can this be done ? 2) Besides the criteria already in effect, if any cell in column A equals 1 AND the corresponding cell in column D is more than 500, then prompt the user on which row(s) is not fulfilling the criteria, allow them to click "OK", then prompt them to give them a choice to save the workbook or don't save and continue working with the workbook. Appreciate any help. Thanks!! "JW" wrote: So, all of those conditions must be met, correct? If so, place this in the ThisWorkbook module of the applicable workbook. Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Dim lRow As Long With Sheets("Sheet1") lRow = .Cells.Find(what:="*", _ After:=.Cells(1, 1), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row For i = 3 To lRow If .Cells(i, 1).Text = "3" Or _ .Cells(i, 2).Text = "Calculus" Then If IsEmpty(.Cells(i, 3)) And _ IsEmpty(.Cells(i, 6)) And _ IsEmpty(.Cells(i, 8)) Then Cancel = True MsgBox "Your message here" Exit For End If End If Next i End With End Sub Clare wrote: Hi, could someone tell me how to do this in vba ? When a user tries to save the workbook, for Sheet1 if any cell in column A (A3 until the last row) equals "3" or column B (B3 until the last row) equals "Calculus" then prevent the workbook from being saved & prompt a msgbox if the corresponding cell in column C , F & H is blank. Thanks ! Rgds, Clare |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can I hyperlink to a worksheet tab in workbook saved as html? | Excel Discussion (Misc queries) | |||
Spell Check in Protected Worksheet & Shared Workbook | Excel Discussion (Misc queries) | |||
Spell Check in Protected Worksheet & Shared Workbook continued | Excel Discussion (Misc queries) | |||
How can I see a copy of a saved workbook before I saved it again? | Excel Worksheet Functions | |||
When saving workbook only the first worksheet gets saved. | Excel Worksheet Functions |