Help in Programming and Making macros work after re-opening file

[Nickys13]

Hello all,

I don't have any experience with the macros programming however I have managed to create the following programme with the help found from older posts here:

I have a spreadsheet with fields for writing down daily expenses so at one of the months (specific worksheet) I want a msgbox to appear with the message ''Happy bday.. etc..'' at an appropriate time. Therefore I have written the following and please advice whether this is a good idea or there are better options:
___________________________________________________________
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.EnableEvents = True
If Not Range("E9").Value = 0 Or Not Range("E16").Value = 0 Then MsgBox " Happy Bday!"

If Not Range("E9").Value = 0 Then Range("E9") = Empty
If Not Range("E16").Value = 0 Then Range("E16") = Empty


End Sub
______________________________________________________________

E9-E15 cells are the expenses entries one day before the birthday and E16-E22 are on the day of the birthday: Therefore the user will write down anything on any of the two cells, then the message will appear and immediately after it will be reset.


Now this had been working only at the time of creating the project. I am saving the file as macro-enabled and when I re-open it, it pops the message of security danger (Enable, Disable macros), I click on enable macros but then nothing seems to be working while the code is still there. Any ideas?

Thanks in advance!

 

[venkat1926]

first decide what you want to do In your mind or on paper you do sort of a flow diagram

there are two types of programmes
1. macro in the module: if this macro is written for some purpose you have to run this macro every time there is change in data
2. event code;. that some code statements will be run automatically when a particular event occurs.. for e.g. your code is for running the code statements automatically when you change entry in a target cell or any cells in a target range. as soon as you change he entry the macro will automatically run. sometimes this will be a nuisance. so be careful in using event code. there are many such events.

a

pplication.enableevents=true

must go last before end sub

this statement is ok if you feel that is what you want

If Not Range("E9").Value = 0 Or Not Range("E16").Value = 0 Then MsgBox " Happy Bday!"

but the following statements are not clear

If Not Range("E9").Value = 0 Then Range("E9") = Empty
    If Not Range("E16").Value = 0 Then Range("E16") = Empty

if you want that if E9 has some entry you want it to be blank then correct statement would be

If Not Range("E9").Value = 0 Then Range("E9") = ""
    If Not Range("E16").Value = 0 Then Range("E16") = ""

can be tweaked ***

if range("E9")<>0 then range("e9")=""

best way of learning to write macro is to decide what do you wnat to do , RECORD the macro when those steps are taken either with the mouse or keyboard and look at this macro statement and edit it wherever necesary.
any doubt place the problem, to this newsgroup. But give some data and clearly explain what you want, kif necessary with examples.

best compliments for trying to learn something new.

 

[Nickys13]

Thanks a lot for your reply. I am using event code. The programme works fine but only for the first time. For example I create a new project, then write the code:


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Range("E9").Value = 0 Or Not Range("E16").Value = 0 Then MsgBox " Happy Birthday !"

If Not Range("E9").Value = 0 Then Range("E9") = ""
If Not Range("E16").Value = 0 Then Range("E16") = ""

Application.EnableEvents = True
End Sub
--------------------------------------------

When I enter the entry on either cell E9, E16, the message pops up, the user reads it, the cells get reset, so that they can press on it any time and still see that message. So far so good.

Then I save the project as .xlsm (macro-enabled). When I close the file and open it again, nothing is working! The code is still there but it seems to be idle.

 

[venkat1926]

when message "happy bday" pops up and you must hit OK then only it goes to the next code

the event code is OK as training. but it is not clear what you what you want to do
list what you data in a series of sentences and then post back

I thin for the purpose you are having event code is not required what you require is a simple macro. Event code has to be written carefully

 

[Nickys13]

Ok this is the idea:

I will send this excel spreadsheet to a person. This has 12 worksheets, one for each month. On each worksheet there are assigned rows for each day to write down the expenses of the day, then Excel calculates the sum.

What I want is a way to pop-up the happy bday message as a surprise at an appropriate time. Therefore I put the code on the assigned worksheet for the particular month (august). I thought to pop up the message when the cell E9 is modified (first expense entry for the day before the birthday,if the person uses the spreadsheet during the night before), or when E16 is modified (first expense entry for the same birthday date).

I have tested it. When I modify either cell E9 the message pops up. Then click ok. The cell gets reset. Then can click again as many times as you would like.

I don't know how to make a macro!

My problem is that this only works once for a new file. When I open the file again it's not working and I don't know why.

Thanks for your help.

 

[venkat1926]

second time I think that condition for pop message does not exist.
so pop message does not come up


suppose somebody" birthday occurs in January
I suggest you can give some simpler condition . you can give these conditions

1)when the workbook opens
or
2) when this sheet is activated

let us take the second choice
put it like this

Private Sub Worksheet_Activate()
If Month(CDate(Date)) = 1 Then MsgBox "happy birthday"
End Sub

only problem is whenever he/she activates the sheet, message will pop up. will not be annoying. of course you can write a little more complicated event code. but for such a trivial matter should we use a macro ???????
just send a sms to his mobile

 

[Nickys13]

s but for such a trivial matter should we use a macro ???????
just send a sms to his mobile

Because it would be a nice unexpected surprise to someone who once meant a lot. I am not going to exactly write the contents of the message box here!!


Anyway I have just discovered the following thing!!

That when I save the file (.xlsm) in my documents folder or other folder such as desktop, then the code is not executed the second time. However experimenting I have saved as a macro tempate format, which automatically got saved into the following directory:

Users--> M.... --> Application Data --> Roaming --> Microsoft --> Templates.

Here all the different files that I have tested are all working and executing the code properly. What is the reason behind this? Any security issues?