VBA pop up message

[Chaozfate]

Dear All,

I have the following VBA running for my excel. The purpose is to serve as a date reminder if my formulated column J on my sheet 3 change to a date.

If works just fine if I last save my excel on sheet 3, the pop up message is working as intended, but however, if i last save my excel on sheet 1, the pop up message won't appear.

Would be appreciated if anyone can help me revised my VBA code following to instruct excel to show the pop up based on the criteria on sheet 3, regardless of my last save is at any sheet.


Private Sub Workbook_open()
Dim r As Long
For r = 2 To 99999
If Range("J" & r).Value = Date Then
MsgBox "There are Products mature today/declaring dividends, please check and post"

Exit Sub
End If
Next r

End Sub


Thanks and regards

 

[CephasOz]

Try (note the "." in front of "Range")

Private Sub Workbook_open()
    Dim r As Long
    With ThisWorkbook.Worksheets("Sheet3")
        For r = 2 To 99999
            If .Range("J" & r).Value = Date Then
                MsgBox "There are Products mature today/declaring dividends, please check and post"
                Exit Sub
            End If
        Next r
    End With
End Sub

 

[Chaozfate]

Wow, it works! would you mind to explain whats with the "." infront of the "range"? haha.

i tried with and without, the "." trigger the pop up message eventhough i last save on other sheet, without the ".", it wont trigger, why is it?

 

[CephasOz]

The "with/end with" statement says that objects that start with a "." refer to what follows the "with" - in this case, Sheet3. So that range isn't just the range in whatever sheet happens to be active, it is always that range in Sheet3.

 

[Chaozfate]

Thank You So Much

 

[CephasOz]

Glad to help.