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

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

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

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

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

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