Reg: Macro - Auto run

Solved/Closed
jee - Dec 30, 2009 at 06:11 AM
 jee - Jan 5, 2010 at 11:51 PM
Hello,
I need to run a macro automatically when i open an excel sheet.
and aslo to run a macro when i close the sheet
can any one help me in this.

regards
jee

5 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jan 2, 2010 at 10:38 PM
I tried in my excel 2002 it works.

let us what could be the problem

go to some other sheet and then select sheet
does this help
If it does not
goto vbeditor (alt+F11)
then in the vbe editor click view and click immediate window
in the immedite window pate
application.enableevents=true
and hit enteer key at the end of the above line.
now again go someother sheet and again come back to sheet 1

The logic is whever you select sheet 1 the macro m will operate. by merely staying in sheet 1 it will not operate. keep this in mind. \\
confirm whether it is ok or still some problem.
1
Hi
ya its working once we select some other and return back to Sheet 1 ..
Thanks a lot venkat ..
its possible to run macro without doing this..
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jan 4, 2010 at 07:47 PM
This is an event code. some event should happen. in this case it is activating a sheet. it can be double click or right click a cell or change in selection of a cell or change in the value of a cell which event do you want the fire the macro?????
the events are:
* Activate
* BeforeDoubleClick
* BeforeRightClick
* Calculate
* Change
* Deactivate
* SelectionChange
1
Fine.Thanks a lot venkat.
now i can trigger a macro by some events.thanks for the information .
take care.
0
aquarelle
Posts
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
491
Jan 5, 2010 at 12:01 PM
Hello,

To automatically run a macro when you open a workbook, you have to add in a module of your workbook (with your example here) :
Sub Auto_Open()
        Activesheet.cells(5,5).value = 67 
End Sub


Best regards
1
Thanks
Venkat...
Take care.
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jan 1, 2010 at 04:56 AM
is it when you open the sheet or open the workbook

I assume when you activate a sheet.


right click the sheet tab and paste these two event codes

Private Sub Worksheet_Activate()
macro name
End Sub


Private Sub Worksheet_Deactivate()
macro name 
End Sub
0
Hi
venkat its not working..
i did the same..

from jee
0

Didn't find the answer you are looking for?

Ask a question
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jan 1, 2010 at 08:14 AM
not clear where was the problem.. was the original macro in standard module
did you introduce the macro name in the space between the lines


Private Sub Worksheet_Activate()

and

end sub

what is your macro. post the macro

and the module number in which the macro is parked.
0
Hi venkat
I did the same .still its not working
my macro is in module1.
just a simple macro(sample)
Sub m()
Activesheet.cells(5,5).value = 67
end sub

i insert the code in sheet1

Private Sub Worksheet_Activate()

m

end sub

but its not working
0