New Workbook

[DavidC]

Simple question, but I cannot find the answer.

I want to run code ONLY when I open up a new workbook from
the template workbook. Once the new workbook has opened
it will set certain values which will then be stored when
the workbook is saved as an xls file. When another
workbook is opened using that same template then the code
will run and fill in values again in the new workbook.
However if the xls file is opened the values that were
stored in the workbook remian and are not updated by the
code.

I have tried the following but it does not seem to work.
(The message is a simple way of showing me that the code
ran, and since the message does not come up then the code
did not run.)


Private Sub App_NewWorkbook(ByVal Wb As Workbook)

response = MsgBox("Opening new workbook", vbOKOnly)
Range("data1").Value = Date

End Sub

Thanks

DavidC

 

[Bob Phillips]

Have you set up an application class and defined App?

 

[keepITcool]

I did some experimenting in xlXP and xl2003

The NewBook event is triggered ONLY when you create
a new standard blank workbook and does not trigger
when you select a template file or "create from existing" option
under the New... menu item.

To catch the creation of a new workbook based on a template
you'll have to monitor the application's workbook_open event.

Within the event you'll have to look at the opened workbook's name.

Assume your template is called : "MyInvoice.xlt" then
the "opened" workbook's name will be: "MyInvoice1"
(the number will increase..)


Option Explicit

'File : Personal.xls or Monitor.xls in xlStart
' : Monitor.xla in Addins

'CodeModule: ThisWorkbook

Dim WithEvents xlApp As Application


Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)

Dim i As Integer
Dim sTemp As String

'check if it has an extention
If Not InStr(Wb.Name, ".") Then
'Remove trailing numbers..
Do
i = i + 1
Loop While IsNumeric(Right$(Wb.Name, i))

sTemp = LCase$(Left$(Wb.Name, Len(Wb.Name) + 1 - i))
Select Case sTemp
Case "invoice"
Call Prepinvoice
Case "reminder"
Call Prepreminder

End Select
End If

End Sub

'Following functions could best be in a normal module
Sub Prepinvoice()
MsgBox "Coding for invoice"
End Sub
Sub Prepreminder()
MsgBox "Coding for reminder"
End Sub









keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[DavidC]

Short answer is no. I have never entered into that realm
of coding yet. I guess from your question that I need to
look at doing this first.

Regards

DavidC

 

[Bob Phillips]

David,

You need to setup the application event class, and initiate it in the
workbook_open event. Below is the sort of code you need, in your case the
newworkbook code is in the class module.

But note vthe response from KeepItCool.

'========================================
Insert a class module, rename it to 'clsAppEvents', with this code

Option Explicit

Public WithEvents App As Application


Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

'your code or a call to your macro

End Sub

'========================================
In ThisWorkbook code module, add this event code

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()

Set AppClass.App = Application

End Sub

 

[DavidC]

Cheers and thanks to both of you.

Regards

DavidC

-----Original Message-----
David,

You need to setup the application event class, and initiate it in the
workbook_open event. Below is the sort of code you need, in your case the
newworkbook code is in the class module.

But note vthe response from KeepItCool.

'========================================
Insert a class module, rename it to 'clsAppEvents', with this code

Option Explicit

Public WithEvents App As Application


Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

'your code or a call to your macro

End Sub

'========================================
In ThisWorkbook code module, add this event code

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()

Set AppClass.App = Application

End Sub


--

HTH

RP




.