Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check my code to use template?
I have created a template to use every two or three days. It's coded on a
Workbook_Open event as ReadOnly, with the data-bearing sheets set to xlVeryHidden. If I'm going to use this as a template, I need to be able to open it from within a macro with all macros disabled and all sheets visible. At the moment, the macro that creates my spreadsheet calls up a new workbook using Set wb3 = Workbooks.Add so I need to replace that with code to call up this template. Do I have the right code to - call up from a macro? Dim wb3 As Workbook wb3 = C:\Documents and Settings\ etc. \filename.xls disable macros? Application.EnableEvents = False make sheets visible? wb3.Sheets(Sheet1).Visible = True wb3.Sheets(Sheet2).Visible = True wb3.Sheets(Sheet3).Visible = True Two further questions about the EnableEvents - (1) will it affect the macro already running, or any other macros called up in the process? do I have to constrain the command somehow to affect only the workbook I'm opening? (2) where is the best place to put the Application.EnableEvents = True? just before I SaveAs? or after? Thank you. Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check my code to use template?
This might get you started:
Option Explicit Sub testme() Dim wb3 As Workbook Dim wks As Worksheet Application.EnableEvents = False Set wb3 = Workbooks.Add(template:="c:\my documents\excel\book1.xls") Application.EnableEvents = True For Each wks In wb3.Worksheets wks.Visible = xlSheetVisible Next wks End Sub Would application.enableevents affect any macro running? If your macro does something that would cause an event to fire, then yes. It'll suppress that event. And it's an application event. It's either on or off--not workbook by workbook. This means that you'll want to be careful where you put it. For the most part, I like to turn it off, do my stuff, and turn it back on. But if you know you're doing something that causes events to fire, you'll want to be more careful. Ed wrote: I have created a template to use every two or three days. It's coded on a Workbook_Open event as ReadOnly, with the data-bearing sheets set to xlVeryHidden. If I'm going to use this as a template, I need to be able to open it from within a macro with all macros disabled and all sheets visible. At the moment, the macro that creates my spreadsheet calls up a new workbook using Set wb3 = Workbooks.Add so I need to replace that with code to call up this template. Do I have the right code to - call up from a macro? Dim wb3 As Workbook wb3 = C:\Documents and Settings\ etc. \filename.xls disable macros? Application.EnableEvents = False make sheets visible? wb3.Sheets(Sheet1).Visible = True wb3.Sheets(Sheet2).Visible = True wb3.Sheets(Sheet3).Visible = True Two further questions about the EnableEvents - (1) will it affect the macro already running, or any other macros called up in the process? do I have to constrain the command somehow to affect only the workbook I'm opening? (2) where is the best place to put the Application.EnableEvents = True? just before I SaveAs? or after? Thank you. Ed -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check Box - Macro code | Excel Discussion (Misc queries) | |||
code to check condition for each row | Excel Discussion (Misc queries) | |||
Please check my code!!!! | Excel Discussion (Misc queries) | |||
Bank Check Template | Excel Discussion (Misc queries) | |||
template for check requests | Excel Worksheet Functions |