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
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 15, 2010 at 07:07 AM
Related:
- Getting reminder in excel/outlook
- Outlook download - Download - Email
- How to refresh outlook - Guide
- Number to words in excel - Guide
- Outlook sign up - Guide
- Emojis in outlook - Guide
3 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 17, 2009 at 05:57 AM
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
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 17, 2009 at 07:35 PM
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
save the file . and again open and see whether you get the signal if B6 is more than 300.
confirm it is ok
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
_________________________________________________________
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.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Apr 15, 2010 at 07:07 AM
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.
Dec 17, 2009 at 01:23 PM