MsgBox with Cell Reference

excel102

New Member
Joined
Jul 28, 2017
Messages
9
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
Code:
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.
 
Last edited:
Upvote 0
What worksheet will hold the cells C3 and F3?
 
Upvote 0
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".
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top