Getting reminder in excel/outlook

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,
I want to get reminder from excel or outlook when the value of a particular cell in the excel crosses a particular limit......like i want to be aware of how much expenditures i am making all through the month and update the excel file everyday....
rent 200
entertainment 100
mobile bill 50
foods 150

Total 500

I want excel to remind me when the value of Total crosses 300 and/or nearer to 400. can anyone help me on this? thanks in advance.

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
suppose your data is like this

item expense
entertainment 250
mobile bill 50
foods 150

Total 450

and total sum is always in B6 which has formula
=sum("B2:B5")
in that case this event code will help
right click the sheet tab and click view code
in the resulting window copy paste this event code
save the file

Now if you change any of the expense items in column B then if it B6 is more than 300 you will get a beep and message

.Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Range("B6") > 300 Then
Beep
MsgBox "you are exceeding your total expenditure"
End If
End Sub
thank you venkat for your help. it's a wonderful work. however, it doesn't show me the warning every time i open the excel file. it just show the message when i give input in the file. if i wanna get that message every time i open the file....hw will i get that????
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
open vb editor (alt+F11)
control+R
in the left hand side vb project window go to your file and double click "thisworkbook" in that file (if thisworkbook is not see then you have to click the +sign on the left of your file name which turns into -(minus sign) and it will give all the sheet names and thisworkbook).
in the resulting window copy this event code

Private Sub Workbook_Open()
If Range("B6") > 300 Then
Beep
MsgBox "you are exceeding your total expenditure"
End If
End Sub


save the file . and again open and see whether you get the signal if B6 is more than 300.

confirm it is ok
Thanks Venkat for your prompt reply. The codes when pasted in the code window look like as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Range("B6") > 300 Then
Beep
MsgBox "you are exceeding your total expenditure"
End If
End Sub
_________________________________________________________
Private Sub Workbook_Open()
If Range("B6") > 300 Then
Beep
MsgBox "you are exceeding your total expenditure"
End If
End Sub


However, when i open the file it does not show me the reminder.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Range("B6") > 300 Then
Beep
MsgBox "you are exceeding your total expenditure"
End If
End Sub
------------------------------------------------
Run the macro den it will work 100%
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
Nayan. you can have only one event code for the workhsheet. as daybreaker suggested keep the first event code only.