Ask your question
Message list Discussion Forum list

Getting reminder in excel/outlook

nayan - Dec 17, 2009 3:52am GMT - Last answer on Dec 18, 2009 4:48am GMT nayan
Report
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.Configuration: Windows XP Firefox 3.5.5
I have an answer
Add comment

Other content from Kioskea on « getting reminder in excel/outlook »

Create outlook reminders with excel worksheetForumOffice software
Outlook Reminder from ExcelForumOffice software
Can't open my 2007 documentForumOffice software
Import contacts from Excel to OutlookTipsInternet
Sending emails from excelForumWindows
Excel: If date >= 30 days, cell change color [Solved]ForumOffice software
Sort by :   Votes | Date | Date descending 4 answers
+0
moins plus
venkat1926 591Posts June 14, 2009Registration date March 19, 2010Last seen Dec 17, 2009 10:57am GMT
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

 Add comment - Permalink (1)
nayan - Dec 17, 2009 6:23pm GMT
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????
Add comment
+0
moins plus
venkat1926 591Posts June 14, 2009Registration date March 19, 2010Last seen Dec 18, 2009 12:35am GMT
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

 Add comment - Permalink (3)
nayan - Dec 18, 2009 4:48am GMT
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.
Add comment
Top
See also
supinfo