Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Workbook
Have you set up an application class and defined App?
-- HTH RP "DavidC" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Workbook
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Workbook
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 -----Original Message----- Have you set up an application class and defined App? -- HTH RP "DavidC" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Workbook
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 "DavidC" wrote in message ... 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 -----Original Message----- Have you set up an application class and defined App? -- HTH RP "DavidC" wrote in message ... 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Workbook
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 "DavidC" wrote in message ... 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 -----Original Message----- Have you set up an application class and defined App? -- HTH RP "DavidC" wrote in message ... 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy an image (or picture) from one workbook to a new sheetin another workbook | Excel Worksheet Functions | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions | |||
Copy a range of cells in an unopened workbook and paste it to the current workbook | Excel Programming | |||
Open a password protected excel workbook from second workbook to fetch data using dynamic connection | Excel Programming | |||
What commands do you use to name a workbook, save a workbook,open a workbook | Excel Programming |