Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Before Save
I want a message box to pop up if there are any orange cells on sheet1. The click event will be BeforeSave. I have some conditional formattin that changes a cell to orange. I don't want the user to be able t save if there is orange on the sheet. The orange can appear i A1:F6000. What does the If statement look like for this scenario? Thanks -- scottnshell ----------------------------------------------------------------------- scottnshelly's Profile: http://www.excelforum.com/member.php...nfo&userid=730 View this thread: http://www.excelforum.com/showthread.php?threadid=57053 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Before Save
Tricky!
See http://www.xldynamic.com/source/xld.CFConditions.html -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "scottnshelly" wrote in message news:scottnshelly.2ccezi_1155244811.1027@excelforu m-nospam.com... I want a message box to pop up if there are any orange cells on sheet1. The click event will be BeforeSave. I have some conditional formatting that changes a cell to orange. I don't want the user to be able to save if there is orange on the sheet. The orange can appear in A1:F6000. What does the If statement look like for this scenario? Thanks. -- scottnshelly ------------------------------------------------------------------------ scottnshelly's Profile: http://www.excelforum.com/member.php...fo&userid=7301 View this thread: http://www.excelforum.com/showthread...hreadid=570535 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Before Save
This should do it:
Private Sub Workbook_BeforeClose(Cancel As Boolean) For Each cell In Range("A1:F6000") If cell.Interior.ColorIndex = 44 Then MsgBox "Sheet has Orange Cells" End If Next End Sub Chas "scottnshelly" wrote: I want a message box to pop up if there are any orange cells on sheet1. The click event will be BeforeSave. I have some conditional formatting that changes a cell to orange. I don't want the user to be able to save if there is orange on the sheet. The orange can appear in A1:F6000. What does the If statement look like for this scenario? Thanks. -- scottnshelly ------------------------------------------------------------------------ scottnshelly's Profile: http://www.excelforum.com/member.php...fo&userid=7301 View this thread: http://www.excelforum.com/showthread...hreadid=570535 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Before Save
Thanks. That was really complicated. Would it be easier if I had a message box if the same condition is met that causes the cells to turn orange? The conditional formatting in A:F is the same: =IF(AND(H2<"",A2=""),TRUE,FALSE) If there is something in H but any one of A:F is blank, I want an error message or prevent them from saving. I have tried to make it clear that columns A:F are required, but they are still leaving some of them blank. Thanks. -- scottnshelly ------------------------------------------------------------------------ scottnshelly's Profile: http://www.excelforum.com/member.php...fo&userid=7301 View this thread: http://www.excelforum.com/showthread...hreadid=570535 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Before Save
CF Chas, not cell colour.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ChasAA" wrote in message ... This should do it: Private Sub Workbook_BeforeClose(Cancel As Boolean) For Each cell In Range("A1:F6000") If cell.Interior.ColorIndex = 44 Then MsgBox "Sheet has Orange Cells" End If Next End Sub Chas "scottnshelly" wrote: I want a message box to pop up if there are any orange cells on sheet1. The click event will be BeforeSave. I have some conditional formatting that changes a cell to orange. I don't want the user to be able to save if there is orange on the sheet. The orange can appear in A1:F6000. What does the If statement look like for this scenario? Thanks. -- scottnshelly ------------------------------------------------------------------------ scottnshelly's Profile: http://www.excelforum.com/member.php...fo&userid=7301 View this thread: http://www.excelforum.com/showthread...hreadid=570535 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Before Save
Have a little macro that checks it
Function AnyOrange() Dim cell As Range For each cell In range("A2:F6000") If cell.value ="" and cell.Offset(0,7) < "" Then AnyOrange = True Exit Function End If Next cell End Function Then just see if the function returns True and don't save if so. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "scottnshelly" wrote in message news:scottnshelly.2cciov_1155249612.0315@excelforu m-nospam.com... Thanks. That was really complicated. Would it be easier if I had a message box if the same condition is met that causes the cells to turn orange? The conditional formatting in A:F is the same: =IF(AND(H2<"",A2=""),TRUE,FALSE) If there is something in H but any one of A:F is blank, I want an error message or prevent them from saving. I have tried to make it clear that columns A:F are required, but they are still leaving some of them blank. Thanks. -- scottnshelly ------------------------------------------------------------------------ scottnshelly's Profile: http://www.excelforum.com/member.php...fo&userid=7301 View this thread: http://www.excelforum.com/showthread...hreadid=570535 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Before Save
Thanks a lot. That did the trick. Keep up the good work. -- scottnshelly ------------------------------------------------------------------------ scottnshelly's Profile: http://www.excelforum.com/member.php...fo&userid=7301 View this thread: http://www.excelforum.com/showthread...hreadid=570535 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
How to diasble save and save as menu but allow a save button | Excel Programming | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
Totally Disabling (^ save ) (Save as) and Save Icon – Which code do I use: | Excel Programming |