• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto run macro when saving an excel file

S

Sushil

Member
Hi, I have macro to update pivot table when save button is clicked. but the problem is if the macro is not enabled before then the macro does not auto run. is it possible to add another code to the below macro which enables the macros first and the auto runs it.


Thanks

Sushil


Sub update()

With ActiveSheet

.Protect Password:="XXXXXX", UserInterfaceOnly:=True

For Each pt In .PivotTables

pt.RefreshTable

Next pt

End With

End Sub


for auto run in the work book


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'to run a macro named update


Call Sheet1.update

End Sub
 
Hi Sushil,


Everything seems to be fine in your code the problem with before save


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Call update

End Sub


You have to use only the macro name.


Thanks,

Suresh Kumar S
 
You can't have code that automatically enables macros, but you can set things up to force users to have macros enabled. See here:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=379
 
Hi Luke,

somehow that macro does not work. As per the instruction, i changed the sheet name to Macros and copy pasted the code in Thisworkbook object. Do i had to add the other sheet names in the code anywhere or change something else in the code. Please let me know.


Thanks

Sushil
 
Hi Sushil!

Try This one..

You can't have code that automatically enables macros, but you can set things up to force users to have macros enabled. See here:
Click to expand...

http://datapigtechnologies.com/blog/index.php/forcing-your-clients-to-enable-macros/


Regards,

Deb
 
Hi Sushil,


If you will read through the link that is posted by Luke then at the bottom you'll find a zip file containing a demo file with all code in it.


Try that file and understand the concept and then implement. It should work.
 
Thanks Guys!. Macro mentioned by Luke works but first time it shows all the sheets. then if macro is enabled and the workbook saved .. after that the macro works as it is supposed to... But now i have another problem ... the auto run macro ( i mentioned on the top) and the macro to force users to enable macros have same line "Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)" which is giving compile error " ambigous name Workbook_BeforeSave.


Can you please help on this.


Thanks

Sushil
 
Should be able to merge the two. I'd do it like this:

[pre]
Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 

'to run a macro named update
Call Sheet1.update

'Turn off events to prevent unwanted loops
Application.EnableEvents = False 

'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
Cancel = True 

'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub
[/pre]
and then you can delete the other (smaller) macro w/ same name.
 
You must log in or register to reply here.
Back
Top