Place an IF Statement into a Macro 2007 and 2003

[Lisa]

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

 

[Bob Phillips]

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)

 

[Jon Peltier]

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

 

[Lisa]

Absolutely perfect. Many thanks for your time both

 

[Lisa]

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

 

[Bob Phillips]

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)