Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro when file is opened and closed
I have an excel file that runs a macro when ever the file is opened or
closed. I did not write the code or create this file. The code of the macro in the file is below. I tried copying the code to a blank excel workbook thinking that the macro would run when the file is opened and closed, but it does not. How does this file run this macro whenever the file is opened or closed? I downloaded the file from http://www.savefile.com/files/495953 Option Explicit Sub ToggleCutCopyAndPaste(Allow As Boolean) 'Activate/deactivate cut, copy, paste and pastespecial menu items Call EnableMenuItem(21, Allow) ' cut Call EnableMenuItem(19, Allow) ' copy Call EnableMenuItem(22, Allow) ' paste Call EnableMenuItem(755, Allow) ' pastespecial 'Activate/deactivate drag and drop ability Application.CellDragAndDrop = Allow 'Application.EnableEvents = Allow ActiveWorkbook.Saved = True 'Activate/deactivate cut, copy, paste and pastespecial shortcut keys With Application Select Case Allow Case Is = False .OnKey "^c", "CutCopyPasteDisabled" .OnKey "^v", "CutCopyPasteDisabled" .OnKey "^x", "CutCopyPasteDisabled" .OnKey "+{DEL}", "CutCopyPasteDisabled" .OnKey "^{INSERT}", "CutCopyPasteDisabled" Case Is = True .OnKey "^c" .OnKey "^v" .OnKey "^x" .OnKey "+{DEL}" .OnKey "^{INSERT}" End Select End With End Sub Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean) 'Activate/Deactivate specific menu item Dim cBar As CommandBar Dim cBarCtrl As CommandBarControl For Each cBar In Application.CommandBars Set cBarCtrl = cBar.FindControl(Id:=ctlId, recursive:=True) If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled Next End Sub Sub CutCopyPasteDisabled() 'Inform user that the functions have been disabled MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro when file is opened and closed
There must be some code in workbook_open event that calls those macros.
You can see such code by right clicking on the Excel icon on the worksheet (or next to the File menu if you maximise your workbooks), select View Code from the menu,. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "mcphc" wrote in message ... I have an excel file that runs a macro when ever the file is opened or closed. I did not write the code or create this file. The code of the macro in the file is below. I tried copying the code to a blank excel workbook thinking that the macro would run when the file is opened and closed, but it does not. How does this file run this macro whenever the file is opened or closed? I downloaded the file from http://www.savefile.com/files/495953 Option Explicit Sub ToggleCutCopyAndPaste(Allow As Boolean) 'Activate/deactivate cut, copy, paste and pastespecial menu items Call EnableMenuItem(21, Allow) ' cut Call EnableMenuItem(19, Allow) ' copy Call EnableMenuItem(22, Allow) ' paste Call EnableMenuItem(755, Allow) ' pastespecial 'Activate/deactivate drag and drop ability Application.CellDragAndDrop = Allow 'Application.EnableEvents = Allow ActiveWorkbook.Saved = True 'Activate/deactivate cut, copy, paste and pastespecial shortcut keys With Application Select Case Allow Case Is = False .OnKey "^c", "CutCopyPasteDisabled" .OnKey "^v", "CutCopyPasteDisabled" .OnKey "^x", "CutCopyPasteDisabled" .OnKey "+{DEL}", "CutCopyPasteDisabled" .OnKey "^{INSERT}", "CutCopyPasteDisabled" Case Is = True .OnKey "^c" .OnKey "^v" .OnKey "^x" .OnKey "+{DEL}" .OnKey "^{INSERT}" End Select End With End Sub Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean) 'Activate/Deactivate specific menu item Dim cBar As CommandBar Dim cBarCtrl As CommandBarControl For Each cBar In Application.CommandBars Set cBarCtrl = cBar.FindControl(Id:=ctlId, recursive:=True) If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled Next End Sub Sub CutCopyPasteDisabled() 'Inform user that the functions have been disabled MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro when file is opened and closed
Ok, that's where it's hiding. Thanks for that Bob
"Bob Phillips" wrote: There must be some code in workbook_open event that calls those macros. You can see such code by right clicking on the Excel icon on the worksheet (or next to the File menu if you maximise your workbooks), select View Code from the menu,. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "mcphc" wrote in message ... I have an excel file that runs a macro when ever the file is opened or closed. I did not write the code or create this file. The code of the macro in the file is below. I tried copying the code to a blank excel workbook thinking that the macro would run when the file is opened and closed, but it does not. How does this file run this macro whenever the file is opened or closed? I downloaded the file from http://www.savefile.com/files/495953 Option Explicit Sub ToggleCutCopyAndPaste(Allow As Boolean) 'Activate/deactivate cut, copy, paste and pastespecial menu items Call EnableMenuItem(21, Allow) ' cut Call EnableMenuItem(19, Allow) ' copy Call EnableMenuItem(22, Allow) ' paste Call EnableMenuItem(755, Allow) ' pastespecial 'Activate/deactivate drag and drop ability Application.CellDragAndDrop = Allow 'Application.EnableEvents = Allow ActiveWorkbook.Saved = True 'Activate/deactivate cut, copy, paste and pastespecial shortcut keys With Application Select Case Allow Case Is = False .OnKey "^c", "CutCopyPasteDisabled" .OnKey "^v", "CutCopyPasteDisabled" .OnKey "^x", "CutCopyPasteDisabled" .OnKey "+{DEL}", "CutCopyPasteDisabled" .OnKey "^{INSERT}", "CutCopyPasteDisabled" Case Is = True .OnKey "^c" .OnKey "^v" .OnKey "^x" .OnKey "+{DEL}" .OnKey "^{INSERT}" End Select End With End Sub Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean) 'Activate/Deactivate specific menu item Dim cBar As CommandBar Dim cBarCtrl As CommandBarControl For Each cBar In Application.CommandBars Set cBarCtrl = cBar.FindControl(Id:=ctlId, recursive:=True) If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled Next End Sub Sub CutCopyPasteDisabled() 'Inform user that the functions have been disabled MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to run a macro automatically when the file is opened? | Excel Discussion (Misc queries) | |||
macro to autofilter other file and copy in opened file | Excel Programming | |||
charting closed vs opened orders | Charts and Charting in Excel | |||
Close file and run macro from newly opened file | Excel Programming | |||
save Wsh to closed/opened WB | Excel Programming |