| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Place an IF Statement into a Macro 2007 and 2003
Hi
Within Sub Workbook_BeforeClose I would like an IF statement to check to cell values within my worksheet. If both conditions are met, prevent the workbook from closing and send the user a message to tell them to correct the condition. If the condition does not exist, continue with closing the workbook An example of what I'm looking for is based on this formula =IF(AND(A1="red",B1="blue"), "Red and Blue cannot exists together, please correct before closing the workbook" etc I am developing my macros in Excel 2007 and will require them to run on 2003 when I deploy them so would appreciate solutions for both (it may be that they are the same?) Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Place an IF Statement into a Macro 2007 and 2003
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook.Worksheets("Sheet1") If .Range("A1").Value = "red" And .Range("B1").Value = blue Then MsgBox "Red and Blue cannot exists together, please " & vbNewLine & _ "correct before closing the workbook", vbOKOnly Cancel = True End If End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lisa" wrote in message ... Hi Within Sub Workbook_BeforeClose I would like an IF statement to check to cell values within my worksheet. If both conditions are met, prevent the workbook from closing and send the user a message to tell them to correct the condition. If the condition does not exist, continue with closing the workbook An example of what I'm looking for is based on this formula =IF(AND(A1="red",B1="blue"), "Red and Blue cannot exists together, please correct before closing the workbook" etc I am developing my macros in Excel 2007 and will require them to run on 2003 when I deploy them so would appreciate solutions for both (it may be that they are the same?) Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Place an IF Statement into a Macro 2007 and 2003
1. It's invariably best to develop in the earliest version of Excel in which
your workbook will be used, in your case 2003. That said, Excel 2007 requires more validation of preexisting code than earlier versions had. 2. The code in 2003 is as follows, and it should work without modification in 2000 through 2007: Private Sub Workbook_BeforeClose(Cancel As Boolean) With ThisWorkbook.Worksheets("Sheet1") If LCase$(.Range("A1").Value) = "red" And LCase$(.Range("B1").Value) = "blue" Then Cancel = True MsgBox "Your message here", vbExclamation + vbOKOnly, "Can't close workbook" End If End With End Sub The line "Cancel = True" is what prevents the workbook from closing (it cancels the workbook close command) so that the user can adjust the values. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Lisa" wrote in message ... Hi Within Sub Workbook_BeforeClose I would like an IF statement to check to cell values within my worksheet. If both conditions are met, prevent the workbook from closing and send the user a message to tell them to correct the condition. If the condition does not exist, continue with closing the workbook An example of what I'm looking for is based on this formula =IF(AND(A1="red",B1="blue"), "Red and Blue cannot exists together, please correct before closing the workbook" etc I am developing my macros in Excel 2007 and will require them to run on 2003 when I deploy them so would appreciate solutions for both (it may be that they are the same?) Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Place an IF Statement into a Macro 2007 and 2003
Absolutely perfect. Many thanks for your time both
"Jon Peltier" wrote: 1. It's invariably best to develop in the earliest version of Excel in which your workbook will be used, in your case 2003. That said, Excel 2007 requires more validation of preexisting code than earlier versions had. 2. The code in 2003 is as follows, and it should work without modification in 2000 through 2007: Private Sub Workbook_BeforeClose(Cancel As Boolean) With ThisWorkbook.Worksheets("Sheet1") If LCase$(.Range("A1").Value) = "red" And LCase$(.Range("B1").Value) = "blue" Then Cancel = True MsgBox "Your message here", vbExclamation + vbOKOnly, "Can't close workbook" End If End With End Sub The line "Cancel = True" is what prevents the workbook from closing (it cancels the workbook close command) so that the user can adjust the values. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Lisa" wrote in message ... Hi Within Sub Workbook_BeforeClose I would like an IF statement to check to cell values within my worksheet. If both conditions are met, prevent the workbook from closing and send the user a message to tell them to correct the condition. If the condition does not exist, continue with closing the workbook An example of what I'm looking for is based on this formula =IF(AND(A1="red",B1="blue"), "Red and Blue cannot exists together, please correct before closing the workbook" etc I am developing my macros in Excel 2007 and will require them to run on 2003 when I deploy them so would appreciate solutions for both (it may be that they are the same?) Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Place an IF Statement into a Macro 2007 and 2003
Ooops, I have another question on this.
How can I expand my cell references (ie A1,B1) to be the whole of column A or B, or a range of cells in column A or B? Alternatively, I could create a formula within the spreadsheet to provide me with the "match" then just look for the word "match" in a single column and send the error as described? Thanks "Lisa" wrote: Absolutely perfect. Many thanks for your time both "Jon Peltier" wrote: 1. It's invariably best to develop in the earliest version of Excel in which your workbook will be used, in your case 2003. That said, Excel 2007 requires more validation of preexisting code than earlier versions had. 2. The code in 2003 is as follows, and it should work without modification in 2000 through 2007: Private Sub Workbook_BeforeClose(Cancel As Boolean) With ThisWorkbook.Worksheets("Sheet1") If LCase$(.Range("A1").Value) = "red" And LCase$(.Range("B1").Value) = "blue" Then Cancel = True MsgBox "Your message here", vbExclamation + vbOKOnly, "Can't close workbook" End If End With End Sub The line "Cancel = True" is what prevents the workbook from closing (it cancels the workbook close command) so that the user can adjust the values. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Lisa" wrote in message ... Hi Within Sub Workbook_BeforeClose I would like an IF statement to check to cell values within my worksheet. If both conditions are met, prevent the workbook from closing and send the user a message to tell them to correct the condition. If the condition does not exist, continue with closing the workbook An example of what I'm looking for is based on this formula =IF(AND(A1="red",B1="blue"), "Red and Blue cannot exists together, please correct before closing the workbook" etc I am developing my macros in Excel 2007 and will require them to run on 2003 when I deploy them so would appreciate solutions for both (it may be that they are the same?) Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Place an IF Statement into a Macro 2007 and 2003
Are you saying if red is anywhere in column A, and blue is anywhere in
column B, or any row as long as they are the same, or something else? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lisa" wrote in message ... Ooops, I have another question on this. How can I expand my cell references (ie A1,B1) to be the whole of column A or B, or a range of cells in column A or B? Alternatively, I could create a formula within the spreadsheet to provide me with the "match" then just look for the word "match" in a single column and send the error as described? Thanks "Lisa" wrote: Absolutely perfect. Many thanks for your time both "Jon Peltier" wrote: 1. It's invariably best to develop in the earliest version of Excel in which your workbook will be used, in your case 2003. That said, Excel 2007 requires more validation of preexisting code than earlier versions had. 2. The code in 2003 is as follows, and it should work without modification in 2000 through 2007: Private Sub Workbook_BeforeClose(Cancel As Boolean) With ThisWorkbook.Worksheets("Sheet1") If LCase$(.Range("A1").Value) = "red" And LCase$(.Range("B1").Value) = "blue" Then Cancel = True MsgBox "Your message here", vbExclamation + vbOKOnly, "Can't close workbook" End If End With End Sub The line "Cancel = True" is what prevents the workbook from closing (it cancels the workbook close command) so that the user can adjust the values. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Lisa" wrote in message ... Hi Within Sub Workbook_BeforeClose I would like an IF statement to check to cell values within my worksheet. If both conditions are met, prevent the workbook from closing and send the user a message to tell them to correct the condition. If the condition does not exist, continue with closing the workbook An example of what I'm looking for is based on this formula =IF(AND(A1="red",B1="blue"), "Red and Blue cannot exists together, please correct before closing the workbook" etc I am developing my macros in Excel 2007 and will require them to run on 2003 when I deploy them so would appreciate solutions for both (it may be that they are the same?) Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using "double equal signs" in place of an IF statement | Excel Worksheet Functions | |||
| adjusting decimal place in protected worksheet in excel 2007 | Excel Worksheet Functions | |||
| I need an academic school calendar for 2006-2007 with a place for | Excel Discussion (Misc queries) | |||
| How do I override fixed decimal place settings in EXcel 2003? | Excel Worksheet Functions | |||
| How do I create a macro button and place anywhere on my sheet? | Excel Discussion (Misc queries) | |||
Hybrid Mode
