Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent the Macros to be opened in 2nd xls. Sheet
Gavin,
Surround the open statement of the book being opened with statements to turn off events: Application.EnableEvents = False ChDir "M:\QUALITY\E-TALK REPORTS" Workbooks.Open Filename:="M:\QUALITY\E-TALK REPORTS\Main E-Talk Query.xls" Application.EnableEvents = True HTH, Bernie MS Excel MVP "Gavin Taylor" wrote in message ... Hi, I have a workbook that runs a piece of code to refreshes query tables from other .xls sheets. However on some terminals in the department this invokes the coerce .xls sheet to open. So I decided to include this in the code for consistence sake. The file that it opens has macros that should be disabled if the file is to run in this manner. I can't seem to find the code to prevent the Macros to be opened in the secondary xls. Sheet. Private Sub Workbook_Open() ChDir "M:\QUALITY\E-TALK REPORTS" Workbooks.Open Filename:="M:\QUALITY\E-TALK REPORTS\Main E-Talk Query.xls" Windows("All Call Center Performance.xls").Activate Sheet3.Select Application.AddIns(2).Installed = True Application.AddIns(3).Installed = True Application.AddIns(7).Installed = True Application.AddIns(9).Installed = True Application.AddIns(10).Installed = True Dim ws As Worksheet Dim qt As QueryTable Dim pt As PivotTable For Each ws In ThisWorkbook.Worksheets For Each qt In ws.QueryTables qt.Refresh False Next qt Next ws For Each ws In ThisWorkbook.Worksheets For Each pt In ws.PivotTables pt.RefreshTable Next pt Next ws Windows("Main E-Talk Query.xls").Activate ActiveWindow.Close Windows("All Call Center Performance.xls").Activate Sheet3.Select Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent the Macros to be opened in 2nd xls. Sheet
Thanks Worked fine.
"Bernie Deitrick" wrote: Gavin, Surround the open statement of the book being opened with statements to turn off events: Application.EnableEvents = False ChDir "M:\QUALITY\E-TALK REPORTS" Workbooks.Open Filename:="M:\QUALITY\E-TALK REPORTS\Main E-Talk Query.xls" Application.EnableEvents = True HTH, Bernie MS Excel MVP "Gavin Taylor" wrote in message ... Hi, I have a workbook that runs a piece of code to refreshes query tables from other .xls sheets. However on some terminals in the department this invokes the coerce .xls sheet to open. So I decided to include this in the code for consistence sake. The file that it opens has macros that should be disabled if the file is to run in this manner. I can't seem to find the code to prevent the Macros to be opened in the secondary xls. Sheet. Private Sub Workbook_Open() ChDir "M:\QUALITY\E-TALK REPORTS" Workbooks.Open Filename:="M:\QUALITY\E-TALK REPORTS\Main E-Talk Query.xls" Windows("All Call Center Performance.xls").Activate Sheet3.Select Application.AddIns(2).Installed = True Application.AddIns(3).Installed = True Application.AddIns(7).Installed = True Application.AddIns(9).Installed = True Application.AddIns(10).Installed = True Dim ws As Worksheet Dim qt As QueryTable Dim pt As PivotTable For Each ws In ThisWorkbook.Worksheets For Each qt In ws.QueryTables qt.Refresh False Next qt Next ws For Each ws In ThisWorkbook.Worksheets For Each pt In ws.PivotTables pt.RefreshTable Next pt Next ws Windows("Main E-Talk Query.xls").Activate ActiveWindow.Close Windows("All Call Center Performance.xls").Activate Sheet3.Select Range("A1").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I prevent users from changing macros? | Excel Discussion (Misc queries) | |||
password protect a spreadsheet to prevent it from being opened. | Excel Worksheet Functions | |||
Workbook opened with macros disabled | Excel Programming | |||
Prevent users from running macros | Excel Programming | |||
Prevent user from disabling macros | Excel Programming |