MsgBox with Cell Reference

[excel102]

I am new to VBA and currently have the following one set-up:

Private Sub Workbook_Open()
MsgBox "Notify Customer 90 days prior to deadline"
MsgBox "Notify Customer when travel has exceeded 75%"
End Sub

However, the notifications pops every time I open the worksheet and that is not too effective for my needs. So,
1. I would like to add a condition for it to open only 90 days prior to cell C3 (assuming cell C3 has a date).
2. Assuming cell F3 contains the funding amount, I would like for the notification to pop only when it has reached 75% of cell F3.

Can anybody please help or provide some insight? Thank you.

 

[JLGWhiz]

If you try to show the message box only at the 90 day mark, it could get bypassed altogether if the mark falls on Saturday or Sunday, so I used a three day span to prevent the skip. However, that means that it will show for three days if the mark is Monday thru Wednesday. For you percentage, you did not define where to find 'it'. You will need to fill that in with the sheet and range reference similar to the F3 reference.

Private Sub Workbook_Open()
If Sheets(1).Range("C3").Value - Date <= 93 And _
Sheets(1).Range("C3").Value >= 90 Then
    MsgBox "Notify Customer 90 days prio to Deadline"
End If
If [COLOR=#ff0000]'it' [/COLOR]/ Sheets(1).Range("F3").Value >= .75 Then
    MsgBox "Notify Customer when travel has exceeded 75%"
End If
End Sub

I also assumed that everything is on the first sheet of the workbook. If not, you will also need to make those corrections in the code.

 

[JoeMo]

What worksheet will hold the cells C3 and F3?

 

[excel102]

JoeMo,

Thank you for your help! You are correct about the date falling on a weekend. Therefore, a 3 or even a 5 day window would be ideal. It's ok if it shows for 3 or 5 consecutive days.
Regarding the percentage one, let's say cell F3 is the sum of F20:F25, I would like for excel to notify me once F3 has reached 75% of that sum. Makes sense?
The worksheet holding C3 and F3 is "Project".

 

[JLGWhiz]

Regarding the percentage one, let's say cell F3 is the sum of F20:F25, I would like for excel to notify me once F3 has reached 75% of that sum. Makes sense?

I don't think this will work very well. Look it over and see if you can make it a little more workable. You can get 75% of F3 easy enough, but it is difficult to get 75% of what F3 will end up being.

 

[excel102]

JLGWhiz,

Thanks again! I can change my worksheet such that F3 is the total amount. Will the code provided above calculate 75% of F3 or will I need to change the code?

 

[excel102]

The code I'm using is as follow:

Private Sub Workbook_Open()
If Sheets(1).Range("C3").Value - Date <= 93 And _
Sheets(1).Range("C3").Value >= 90 Then
MsgBox "Notify Customer 90 days prio to Deadline"
End If
If Sheets(1).Range("N4").Value >= 0.75 Then
MsgBox "Notify Customer when travel has exceeded 75%"
End If
End Sub



I moved F3 to N4. However, I have exceeded 75% of N4 and I'm not getting notified. Any suggestions?

 

[excel102]

The code I'm using is as follow:

Private Sub Workbook_Open()
If Sheets(1).Range("C3").Value - Date <= 93 And _
Sheets(1).Range("C3").Value >= 90 Then
MsgBox "Notify Customer 90 days prio to Deadline"
End If
If Sheets(1).Range("N4").Value >= 0.75 Then
MsgBox "Notify Customer when travel has exceeded 75%"
End If
End Sub



I moved F3 to N4. However, I have exceeded 75% of N4 and I'm not getting notified. Any suggestions?