Alert/Message Box

[izet99]

Hi, wonder if somebody can help me setup alert/message box based on date...

I have 17 sheets, each sheet is the same format... in H7 on each sheet, I have date, its date stamp when workscope is updates.

If possible, if H7 is within last 7 date, initiated alert popup message to a user, once sheet is open. Just want to alert user that sheet is update and they need to look for update at the bottom of the sheet... I start with code below but it does really work the way I need to. Any suggestion and help would be greatly appreciated.



Sub msgAlertWSupdate()
With Sheets("Date")
If .Range("H1").Value - Date < 7 Then
MsgBox "This module have updated workscope, please see bottom of the page for update" & vbCrLf & "Update On:" & vbTab & .Range("K1").Value ")
End If
End With
End Sub


Regards,
Izet

[ranman256]

what about...



Sub msgAlertWSupdate()
Dim sht As Worksheet
Dim vMsg
For Each sht In Sheets
sht.Activate
With sht
If DateDiff("d", .Range("H1").Value, Date) < 7 Then
'MsgBox "This module have updated workscope, please see bottom of the page for update" & vbCrLf & "Update On:" & vbTab & .Range("K1").Value
vMsg = vMsg & sht.Name & vbCrLf
End If
End With
Next
If vMsg <> "" Then MsgBox vMsg, , "These modules have updated workscope"
Set sht = Nothing
End Sub

[izet99]

Hi Ranman,

Thanks for feedback and updated code, however when I run code it get stock on "if Datediff(" line. Also, what does "d" represent on that line.... column, date??? Type Mismatched, 13 error when running code.

Regards,
Izet

[izet99]

Hi Ranman,

Not sure but is it possible that I'm getting these error due to the fact that I have another few sheets that H7 cell type is not date...

Would it be possible to add, array of sheet names for code to loop through, if sheet is visible, check for date, if less than 7 day old, alert message would popup... this way I would assign specific sheet to look for only. For example, Sheets, (CFF, CRF, ENG, LPTR...etc) 17 in total...

Regards,
Izet

[Aussiebear]

I have 17 sheets, each sheet is the same format... in H7 on each sheet, I have date, its date stamp when workscope is updates.

but now you say


Not sure but is it possible that I'm getting these error due to the fact that I have another few sheets that H7 cell type is not date...

You need to be sure about what you are asking for.

[izet99]

Hi Aussiebear,

My apologies, you're right... did not initially mention other 3 sheet that I'm using as database to populate those 17 sheets based on multiple condition. I guess I did not realized that those sheets might cause the issue, I'm sorry about that...

Regards,
Izet

[p45cal]

try putting the following in the ThisWorkbook code-module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
For Each sht In Sheets(Array("CFF", "CRF", "ENG", "HPTR")) 'add other sheetnames here.
If Sh Is sht Then
If DateDiff("d", Sh.Range("H7").Value, Date) < 7 Then 'adjust the 7 to something else for threshold.
MsgBox "look for update at the bottom of the sheet…"
End If
End If
Next sht
End Sub

[izet99]

Hi p45cal,

Code worked perfectly, just as I intended, thank you very much. Also, thank you to all other guys for your support and inputs on this case.

How Solved is assigned to the case?

Regards,
Izet

[Aussiebear]

Go to the Thread tools dropdown just above the initial post and select it from there