event macro on a condition
 

I am looking for a event macro, whenever I open that w/sheet (say sheet1) ,if
a datevalue in col b2 does not exists in next sheet col a:a(entire column) a
msg"proceed further" if yes invoke another macro (that already exists inthis
w/book module),if no exit macro,if datevalue exists this macro should not be
invoked.thanks if any body do needgul.

event macro on a condition
 

Option Explicit

Private Sub Worksheet_Activate()
Dim iNextSheet As Long
Dim iLastRow As Long
Dim i As Long
Dim fDate As Boolean
Dim ans As Long
iNextSheet = ActiveSheet.Index + 1
If iNextSheet ThisWorkbook.Worksheets.Count Then
MsgBox "There is no other sheet"
Else
With Worksheets(iNextSheet)
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
fDate = False
For i = 1 To iLastRow
If IsDate(.Cells(i, "A").Value) Then
fDate = True
Exit For
End If
Next i
End With

If fDate Then
ans = MsgBox("Proceed?", vbYesNo)
If ans = vbYes Then other_macro
End If
End If
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"TUNGANA KURMA RAJU" wrote in
message ...
I am looking for a event macro, whenever I open that w/sheet (say sheet1)
,if
a datevalue in col b2 does not exists in next sheet col a:a(entire column)
a
msg"proceed further" if yes invoke another macro (that already exists
inthis
w/book module),if no exit macro,if datevalue exists this macro should not
be
invoked.thanks if any body do needgul.

event macro on a condition
 

Thanks MR.Bob,the code not working,It is not checking the datevalue of col b
of active sheet where I copied this code in worksheet module,its infenitely
activating that other_macro whenever I am opening that sheet.I put date value
in col b of active sheet as dd-mmm-yy format ,is this causing any problem?

"Bob Phillips" wrote:

Option Explicit

Private Sub Worksheet_Activate()
Dim iNextSheet As Long
Dim iLastRow As Long
Dim i As Long
Dim fDate As Boolean
Dim ans As Long
iNextSheet = ActiveSheet.Index + 1
If iNextSheet ThisWorkbook.Worksheets.Count Then
MsgBox "There is no other sheet"
Else
With Worksheets(iNextSheet)
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
fDate = False
For i = 1 To iLastRow
If IsDate(.Cells(i, "A").Value) Then
fDate = True
Exit For
End If
Next i
End With

If fDate Then
ans = MsgBox("Proceed?", vbYesNo)
If ans = vbYes Then other_macro
End If
End If
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"TUNGANA KURMA RAJU" wrote in
message ...
I am looking for a event macro, whenever I open that w/sheet (say sheet1)
,if
a datevalue in col b2 does not exists in next sheet col a:a(entire column)
a
msg"proceed further" if yes invoke another macro (that already exists
inthis
w/book module),if no exit macro,if datevalue exists this macro should not
be
invoked.thanks if any body do needgul.