Previous Topic Next Topic

Getting reminder in excel/outlook

Closed
nayan - Dec 16, 2009 at 10:52 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 15, 2010 at 07:07 AM
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.
Related:

3 responses

Answer 1 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 17, 2009 at 05:57 AM
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
0
nayan
Dec 17, 2009 at 01:23 PM
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????
0
Answer 2 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 17, 2009 at 07:35 PM
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
0
nayan
Dec 17, 2009 at 11:48 PM
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.
0
daybreaker
Apr 15, 2010 at 03:47 AM
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%
0
Answer 3 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 15, 2010 at 07:07 AM
Nayan. you can have only one event code for the workhsheet. as daybreaker suggested keep the first event code only.
0