Excel VBA Scheduled message pop up box reminder

Question

I want to create a script that sends a msg box at a specific time on a specific day of the week. eg every Monday at 10am.

Sub Reminder()
    
    Dim time As Range
    Set time = Sheets("Main").Range("W11")

    
    If time.Value = "Monday 10:00" Then
        MsgBox "Time reminder"
    End If
    
    
End Sub

Tp try and make this work, I have a cell that's formatted to show the day and time in "W11" that's formatted to match the if function, but still doesn't seem to want to work. Note: I had it almost working before but not sure what I was doing differently, however the msgbox would pop up every time I clicked for the whole minute and wasn't only one instance. It seems like it should be really simple.

Answer 1

What is the exact contents of W11? When formatting a date in Excel, the actual contents doesn't change, only the appearance of that date. The content might be "1/1-2023 10:00", and the appearance "Monday 10:00". Which means that W11 is not equal to "Monday 10:00".

Also, the current code will never work as intended, unless the code is run at exactly monday at 10 am. You will need a time range, check if current date/time is larger than monday 10:00 or a constantly listinging "listener" to do that.

Provide some sample data, if you need further assistance.