Messagebox to remind user of 12 day limit

[georgedaws]

Good evening, (or morning)

I have a workbook (below) which alerts a user when 12 days or more has elapsed from the "Date out" column

It almost works but I cannot get the messagebox to stop when a date is entered in the "date return" column.

The other problem is that if there are a few rows with "date return" missing, (it will be used daily so a lot of data going in), many message boxes will end up appearing as the user is trying to work. I thought the macro may be triggered as user tries to save and/or close the workbook.

If anyone can take a look and help me out, I would be grateful.

Thanks

[mdmackillop]

I almost understand this, but not quite. In simple terms what should happen? You only have one row of data. It's hard to see what happens in other cases.

[nepotist]

I don't understand a few thing, you mention "date return" but it doesn't appear to be used in your macro. Why not have function to update your comment cells in this case COLUMN j using a function.
I kinda modified your code to what I am trying to explain.

[georgedaws]

Hello again mdmackillop!

I put only one row on because if I had many rows the message box fires up so many times. It would have been very annoying for someone having a look at it.

Simple terms; day counter counts from "date out". When date counter gets to 12 days it alerts the user the request hasn't been dealt with and to supply a comment. A comment can buy time, but the message box will keep "nagging" until a "date return" has been entered.

I have thought that maybe the messagebox should pop up with every consequential day that goes by until the return date has been entered.

That messagebox is there to help remind someone that "you haven't resolved this and twelve days have gone by. You can buy time with a comment but I won't go away until the (resolved) date is entered.

sorry if I've over waffled!

Thanks

[mdmackillop]

Try a WorkBook Close macro
[VBA]Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rng As Range, chk as Long
Set rng = Sheets(1).Columns(11)
If Application.Max(rng) > 11 Then
chk = MsgBox("Do you wish to update?", vbQuestion + vbYesNo, "Update required")
If chk = vbYes Then Cancel = True
End If
End Sub
[/VBA]

[georgedaws]

Hi nepotist,

Thanks for your help with this. It's difficult for me to explain what it is I am trying to achieve with this.

you are right, the code is not "geared up" for the date returned column but it is needed in order for the messages to stop alerting the user.

The user can basically get away with putting in a comment each time but the message will pop up on a daily basis (hence the column with todays date) until the date (which makes that user accountable) is entered.

The existing workbook does that pretty much except it doesn't have a "stop" point.

[georgedaws]

Hi mdmackillop.

With the code you posted. Being new please bear with me but do I replace your with the existing code? I have tried it with the original workbook and it does not activate on close.

Thanks

[mdmackillop]

Place it in ThisWorkbook module

[Rob342]

George

Have made a mod slight mod to MD code

Checks if date return empty

[VBA]
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rng As Range, chk As Long
Dim Range As Date
Set rng = Sheets(1).Columns(11)
If Application.Max(rng) > 11 And Application.Range("I4") = "" Then
chk = MsgBox("Do you wish to update?", vbQuestion + vbYesNo, "Update required")
If chk = vbYes Then Cancel = True
End If
End Sub

[/VBA]

Hope you didn't mind MD

Rob

[georgedaws]

Hi folks, than you for your replies.

I have put up another example with an ammended formula and a more thorough explantion.

I hope it makes sense.


Thanks

[Rob342]

George

The code that MD gave you works fine and the additional bit i put in was to chk "If the date return was blank & the days are > 11 then show message box.

Have you ran the code & put it before Before close event ??

Rob

[Rob342]

George

Have posted back your workbook with code in the correct place ok

[mdmackillop]

Hope you didn't mind MD

Rob

Happy to offload the burden!

[Rob342]

Thanks MD

Some funny things going on with that workbook ????

rob

[georgedaws]

Gents, thanks for your help with this. Sorry for the delayed reply.

[Rob342]

George

Had another look at your lastest book
have added some code to your version if you want to try
this is the code put it in the before Close workbook event ok

[vba]
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lastrow As Long, icell As Long
Dim lognumber As String, Msg As String, Msg2 As String
Dim ibox As Variant
Dim Daycount As String
lastrow = Range("K" & Rows.Count).End(xlUp).Row
For icell = 4 To lastrow
lognumber = Range("A" & icell).Value
Daycount = Range("K" & icell).Value
If Range("K" & icell).Value > 11 And Range("I" & icell).Value = "" Then ' test for blank on date return
Msg = MsgBox(Daycount & " days have now elapsed for log number " & lognumber & ", would you like to enter a comment now.", vbYesNo, "Error")
If Msg = vbYes Then
ibox = Application.InputBox("Please enter your comment in the box.", "Comments")
'err handling
If ibox = False Then
GoTo Nextone
ElseIf ibox = "" Then
Msg2 = MsgBox("You must enter a comment.", vbOK, "Error")
GoTo Nextone
End If
Range("J" & icell).Value = ibox
Range("K" & icell).Value = Daycount & " *"
ElseIf Msg = vbNo Then
'nothing
End If
End If
Nextone:
Next icell
End Sub
[/vba]