| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assign a macro to the save button
Hi,
I have just learnt how to use macro's and i was wondering if you could assign the macro so it runs everytime I press the save button. I would only want the macro to work on this worksheet, no others!!! Here is my macro below (Very simple), to protect all work sheets when the button is pressed. But i would like this to be assign to the save button Sub Macro3() ' ' Macro3 Macro ' ' Sheets("Sheet3").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Sheet2").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Sheet1").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Hope you can help!!!!! -- Jonno |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Assign a macro to the save button
From workbook launch VBE using short-key Alt+F11. On the left treeview under
'VBA Project(workbookname.xls) Microsoft Excel Objects double click 'This Workbook' and paste the below code to the code panel. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call Macro3 End Sub This is the BeforeSave event which triggers just before you save the workbook. Call Macro3 will call your macro which exists in one of the modules..of the same workbook If this post helps click Yes --------------- Jacob Skaria "Jonno" wrote: Hi, I have just learnt how to use macro's and i was wondering if you could assign the macro so it runs everytime I press the save button. I would only want the macro to work on this worksheet, no others!!! Here is my macro below (Very simple), to protect all work sheets when the button is pressed. But i would like this to be assign to the save button Sub Macro3() ' ' Macro3 Macro ' ' Sheets("Sheet3").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Sheet2").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Sheet1").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Hope you can help!!!!! -- Jonno |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Assign a macro to the save button
This works great thank you very much.
What would i do if i wanted to add more macros before saving I.e Macro 1 or 2, or both aswell? -- Jonno "Jacob Skaria" wrote: From workbook launch VBE using short-key Alt+F11. On the left treeview under 'VBA Project(workbookname.xls) Microsoft Excel Objects double click 'This Workbook' and paste the below code to the code panel. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call Macro3 End Sub This is the BeforeSave event which triggers just before you save the workbook. Call Macro3 will call your macro which exists in one of the modules..of the same workbook If this post helps click Yes --------------- Jacob Skaria "Jonno" wrote: Hi, I have just learnt how to use macro's and i was wondering if you could assign the macro so it runs everytime I press the save button. I would only want the macro to work on this worksheet, no others!!! Here is my macro below (Very simple), to protect all work sheets when the button is pressed. But i would like this to be assign to the save button Sub Macro3() ' ' Macro3 Macro ' ' Sheets("Sheet3").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Sheet2").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Sheet1").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Hope you can help!!!!! -- Jonno |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Assign a macro to the save button
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call Macro1 Call Macro2 Call Macro3 End Sub If this post helps click Yes --------------- Jacob Skaria "Jonno" wrote: This works great thank you very much. What would i do if i wanted to add more macros before saving I.e Macro 1 or 2, or both aswell? -- Jonno "Jacob Skaria" wrote: From workbook launch VBE using short-key Alt+F11. On the left treeview under 'VBA Project(workbookname.xls) Microsoft Excel Objects double click 'This Workbook' and paste the below code to the code panel. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call Macro3 End Sub This is the BeforeSave event which triggers just before you save the workbook. Call Macro3 will call your macro which exists in one of the modules..of the same workbook If this post helps click Yes --------------- Jacob Skaria "Jonno" wrote: Hi, I have just learnt how to use macro's and i was wondering if you could assign the macro so it runs everytime I press the save button. I would only want the macro to work on this worksheet, no others!!! Here is my macro below (Very simple), to protect all work sheets when the button is pressed. But i would like this to be assign to the save button Sub Macro3() ' ' Macro3 Macro ' ' Sheets("Sheet3").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Sheet2").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Sheet1").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Hope you can help!!!!! -- Jonno |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Assign a macro to the save button
Great thank you very much!!!!!
-- Jonno "Jacob Skaria" wrote: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call Macro1 Call Macro2 Call Macro3 End Sub If this post helps click Yes --------------- Jacob Skaria "Jonno" wrote: This works great thank you very much. What would i do if i wanted to add more macros before saving I.e Macro 1 or 2, or both aswell? -- Jonno "Jacob Skaria" wrote: From workbook launch VBE using short-key Alt+F11. On the left treeview under 'VBA Project(workbookname.xls) Microsoft Excel Objects double click 'This Workbook' and paste the below code to the code panel. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call Macro3 End Sub This is the BeforeSave event which triggers just before you save the workbook. Call Macro3 will call your macro which exists in one of the modules..of the same workbook If this post helps click Yes --------------- Jacob Skaria "Jonno" wrote: Hi, I have just learnt how to use macro's and i was wondering if you could assign the macro so it runs everytime I press the save button. I would only want the macro to work on this worksheet, no others!!! Here is my macro below (Very simple), to protect all work sheets when the button is pressed. But i would like this to be assign to the save button Sub Macro3() ' ' Macro3 Macro ' ' Sheets("Sheet3").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Sheet2").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Sheet1").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Hope you can help!!!!! -- Jonno |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Assign a macro to a button | Excel Discussion (Misc queries) | |||
| Assign a macro to a command button | Excel Discussion (Misc queries) | |||
| How do I Assign Macro to a button? | Charts and Charting in Excel | |||
| How to assign a macro to a commnd button | Excel Discussion (Misc queries) | |||
| assign a macro to a control button | Excel Discussion (Misc queries) | |||
Linear Mode
