Pop up message

[josros60]

Hi,

have these codes to remind me bill payments (this workbook has two sheets) the problem having if I am working the other sheet when times for the pop up get error message is there any way the message pop up even I am the other sheet.

codes:

Sub popup()Dim lstRow As Long
Dim i As Long
Dim msg As String
msg = "The following items are almost due" & vbCrLf & vbCrLf
lstRow = Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To lstRow
    If Range("A" & i) - Date <= 3 Or Range("A" & i) - Date < 0 Then
     msg = msg & Range("B" & i).Value & "  " & "due in " & " " & Range("A" & i) - Date & " Days" & "  " & Format(Range("C" & i).Value, "$#,##0.00;($#,##0.00)") & vbCrLf
     End If
Next i
MsgBox msg
Call settimer
End Sub

Sub settimer()Application.OnTime Now + TimeValue("02:00:00"), "popup"
End Sub

thanks

 

[Fluff]

Try

Sub popup()
Dim lstRow As Long
Dim i As Long
Dim msg As String
msg = "The following items are almost due" & vbCrLf & vbCrLf
With Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
   lstRow = .Range("C" & Rows.count).End(xlUp).Row
   For i = 2 To lstRow
       If .Range("A" & i) - Date <= 3 Or .Range("A" & i) - Date < 0 Then
        msg = msg & .Range("B" & i).Value & "  " & "due in " & " " & .Range("A" & i) - Date & " Days" & "  " & Format(.Range("C" & i).Value, "$#,##0.00;($#,##0.00)") & vbCrLf
        End If
   Next i
End With
MsgBox msg
Call settimer
End Sub

Change sheet name to suit

 

[DanteAmor]

Try this, change "Sheet1" for the sheet name

Sub popup()
  Dim lstRow As Long, i As Long, msg As String
  Dim sh As Worksheet
  Set sh = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
  msg = "The following items are almost due" & vbCrLf & vbCrLf
  lstRow = sh.Range("C" & Rows.Count).End(xlUp).Row
  For i = 2 To lstRow
    If sh.Range("A" & i) - Date <= 3 Or sh.Range("A" & i) - Date < 0 Then
      msg = msg & sh.Range("B" & i).Value & "  " & "due in " & " " & sh.Range("A" & i) - Date & " Days" & "  " & Format(sh.Range("C" & i).Value, "$#,##0.00;($#,##0.00)") & vbCrLf
    End If
  Next i
  MsgBox msg
  Call settimer
End Sub

 

[josros60]

Hi,

I tried but still if I am working the other tab it popup but get this error message Run-time error 13, type mismatch.

Sub popup()Dim lstRow As Long
Dim i As Long
Dim msg As String
Dim sh As Worksheet
[COLOR=#ff0000]  Set sh = Sheets("Bill Payment Schedule")[/COLOR]
msg = "The following items are almost due" & vbCrLf & vbCrLf
lstRow = Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To lstRow
    If Range("A" & i) - Date <= 3 Or Range("A" & i) - Date < 0 Then
     msg = msg & Range("B" & i).Value & "  " & "due in " & " " & Range("A" & i) - Date & " Days" & "  " & Format(Range("C" & i).Value, "$#,##0.00;($#,##0.00)") & vbCrLf
     End If
Next i
MsgBox msg
Call settimer
End Sub

 

[DanteAmor]

But you didn't copy the complete macro, the references to the sheet were missing:

Sub popup()
  Dim lstRow As Long, i As Long, msg As String
  Dim sh As Worksheet
  Set [COLOR=#0000ff]sh [/COLOR]= Sheets("Bill Payment Schedule")
  msg = "The following items are almost due" & vbCrLf & vbCrLf
  lstRow = [COLOR=#0000ff][B]sh[/B][/COLOR].Range("C" & Rows.Count).End(xlUp).Row
  For i = 2 To lstRow
     If [COLOR=#0000ff][B]sh[/B][/COLOR].Range("A" & i) - Date <= 3 Or [COLOR=#0000ff][B]sh[/B][/COLOR].Range("A" & i) - Date < 0 Then      
msg = msg & [COLOR=#0000ff][B]sh[/B][/COLOR].Range("B" & i).Value & "  " & "due in " & " " & [COLOR=#0000ff][B]sh[/B][/COLOR].Range("A" & i) - Date & " Days" & "  " & Format([COLOR=#0000ff][B]sh[/B][/COLOR].Range("C" & i).Value, "$#,##0.00;($#,##0.00)") & vbCrLf
    End If
    End If
  Next i
  MsgBox msg
  Call settimer
End Sub

 

[josros60]

Hi,

can you please highlight for me the references I am missing (in the workbook has two sheets one Bill Payments Schedule and the Prorate)

I am in prorate sheet and pop up the message when 2 hours are due and post your new code still getting the message time-run 13 mismatch.

and this is the line it highlight when the error:

If Range("A" & i) - Date <= 3 Or Range("A" & i) - Date < 0 Then

thanks,

 

[Fluff]

How about

Sub popup()
Dim lstRow As Long
Dim i As Long
Dim msg As String
msg = "The following items are almost due" & vbCrLf & vbCrLf
With Sheets("[COLOR=#ff0000]Bill Payments Schedule[/COLOR]")
   lstRow = .Range("C" & Rows.count).End(xlUp).Row
   For i = 2 To lstRow
       If .Range("A" & i) - Date <= 3 Or .Range("A" & i) - Date < 0 Then
        msg = msg & .Range("B" & i).Value & "  " & "due in " & " " & .Range("A" & i) - Date & " Days" & "  " & Format(.Range("C" & i).Value, "$#,##0.00;($#,##0.00)") & vbCrLf
        End If
   Next i
End With
MsgBox msg
Call settimer
End Sub

The part in red must match the sheet name exactly.

 

[DanteAmor]

Hi,

can you please highlight for me the references I am missing (in the workbook has two sheets one Bill Payments Schedule and the Prorate)

I am in prorate sheet and pop up the message when 2 hours are due and post your new code still getting the message time-run 13 mismatch.

and this is the line it highlight when the error:


thanks,

Just copy the complete macro and rename the sheet

Sub popup()
  Dim lstRow As Long, i As Long, msg As String
  Dim sh As Worksheet
  Set sh = Sheets("Sheet1")
  msg = "The following items are almost due" & vbCrLf & vbCrLf
  lstRow = sh.Range("C" & Rows.Count).End(xlUp).Row
  For i = 2 To lstRow
    If sh.Range("A" & i) - Date <= 3 Or sh.Range("A" & i) - Date < 0 Then
      msg = msg & sh.Range("B" & i).Value & "  " & "due in " & " " & sh.Range("A" & i) - Date & " Days" & "  " & Format(sh.Range("C" & i).Value, "$#,##0.00;($#,##0.00)") & vbCrLf
    End If
  Next i
  MsgBox msg
  Call settimer
End Sub

The error : "time-run 13 mismatch", is because you are subtracting a value - Date, but the value in the cell is a text, so send the error.Review the column data. Maybe you have texts or spaces.

Try this:

(But copy the full macro!)

Sub popup()
  Dim lstRow As Long, i As Long, msg As String
  Dim sh As Worksheet
  Set sh = Sheets("Sheet1")
  msg = "The following items are almost due" & vbCrLf & vbCrLf
  lstRow = sh.Range("C" & Rows.Count).End(xlUp).Row
  For i = 2 To lstRow
    If IsDate(sh.Range("A" & i)) Then
      If sh.Range("A" & i) - Date <= 3 Or sh.Range("A" & i) - Date < 0 Then
        msg = msg & sh.Range("B" & i).Value & "  " & "due in " & " " & sh.Range("A" & i) - Date & " Days" & "  " & Format(sh.Range("C" & i).Value, "$#,##0.00;($#,##0.00)") & vbCrLf
      End If
    End If
  Next i
  MsgBox msg
  Call settimer
End Sub

 

[josros60]

thank you.

 

[DanteAmor]

Glad to help you, thanks for the feedback.