software

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
msgquot;proceed furtherquot; 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.

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 gt; ThisWorkbook.Worksheets.Count Then
MsgBox quot;There is no other sheetquot;
Else
With Worksheets(iNextSheet)
iLastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row
fDate = False
For i = 1 To iLastRow
If IsDate(.Cells(i, quot;Aquot;).Value) Then
fDate = True
Exit For
End If
Next i
End With

If fDate Then
ans = MsgBox(quot;Proceed?quot;, 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)quot;TUNGANA KURMA RAJUquot; gt; wrote in
message ...
gt; I am looking for a event macro, whenever I open that w/sheet (say sheet1)
,if
gt; a datevalue in col b2 does not exists in next sheet col a:a(entire column)
a
gt; msgquot;proceed furtherquot; if yes invoke another macro (that already exists
inthis
gt; w/book module),if no exit macro,if datevalue exists this macro should not
be
gt; invoked.thanks if any body do needgul.
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?

quot;Bob Phillipsquot; wrote:

gt; Option Explicit
gt;
gt; Private Sub Worksheet_Activate()
gt; Dim iNextSheet As Long
gt; Dim iLastRow As Long
gt; Dim i As Long
gt; Dim fDate As Boolean
gt; Dim ans As Long
gt; iNextSheet = ActiveSheet.Index 1
gt; If iNextSheet gt; ThisWorkbook.Worksheets.Count Then
gt; MsgBox quot;There is no other sheetquot;
gt; Else
gt; With Worksheets(iNextSheet)
gt; iLastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row
gt; fDate = False
gt; For i = 1 To iLastRow
gt; If IsDate(.Cells(i, quot;Aquot;).Value) Then
gt; fDate = True
gt; Exit For
gt; End If
gt; Next i
gt; End With
gt;
gt; If fDate Then
gt; ans = MsgBox(quot;Proceed?quot;, vbYesNo)
gt; If ans = vbYes Then other_macro
gt; End If
gt; End If
gt; End Sub
gt;
gt; 'This is worksheet event code, which means that it needs to be
gt; 'placed in the appropriate worksheet code module, not a standard
gt; 'code module. To do this, right-click on the sheet tab, select
gt; 'the View Code option from the menu, and paste the code in.
gt;
gt;
gt; --
gt;
gt; HTH
gt;
gt; RP
gt; (remove nothere from the email address if mailing direct)
gt;
gt;
gt; quot;TUNGANA KURMA RAJUquot; gt; wrote in
gt; message ...
gt; gt; I am looking for a event macro, whenever I open that w/sheet (say sheet1)
gt; ,if
gt; gt; a datevalue in col b2 does not exists in next sheet col a:a(entire column)
gt; a
gt; gt; msgquot;proceed furtherquot; if yes invoke another macro (that already exists
gt; inthis
gt; gt; w/book module),if no exit macro,if datevalue exists this macro should not
gt; be
gt; gt; invoked.thanks if any body do needgul.
gt;
gt;
gt;