Check my code to use template?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

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
 
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.
 
Back
Top