I'm trying to put into Excel a procedure that I have put into Word
(through the assistance of Greg Maxey). There seems to be two issues:
making some procedure operate by default, and using the correct code.

For the first problem, I followed the online help and tried to put the
code in a workbook and then saved the workbook as
\\%userprofile%\application data\microsoft\templates\book.xlt . When
that did not work, I saved it as c:\program files\microsoft
office\office11\xlstart\book.xlt . That did not work either, so I
don't know if the process was incorrect or if the code--which is
verbatim the same as I used in Word--is incorrect (since I know next to
nothing about VBA), or something else is incorrect.

Here's the code I used:

Sub AutoOpen()
If ActiveDocument.BuiltInDocumentProperties("Title") = "" Then
With Dialogs(wdDialogFileSummaryInfo)
.Show
End With
End If
End Sub

Sub AutoClose()
If ActiveDocument.BuiltInDocumentProperties("Title") = "" Then
With Dialogs(wdDialogFileSummaryInfo)
.Show
End With
End If
End Sub

So... can anyone point me in the right direction on making Excel
always prompt for a Title if the Title properties field is blank? (And
the reason I need it on both open and close has to do with automatic
document creation generated sometimes by a different program.)

TIA

Hi Joseph,

I don't know about the path issue,
I avoid paths with spaces.

As for the second part of your question,
there are subtle differences between WordVBA and ExcelVBA.

And subtle differences are harder to control then obvious ones.

This one works for me:

Sub auto_close()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub

Sub auto_open()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub

AutoOpen (Word) vs. Auto_Open (Excel)
Autoclose (Word) vs. Auto_Close (Excel)

And many more differences...

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

on strange territory

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Thanks Helmut. However, that code does not work for me.

I pasted it into the VB editor area of a workbook, making sure that it
was the workbook itself and not a template or add-in to which I was
adding the code. I digitally signed it with an accepted certificate,
since my macro security is set to "high," then I saved the workbook as
book.xlt . Following Microsoft's instructions, I first saved it in the
location of my templates, but that didn't work. Then I tried saving it
in the program's folder for startup templates, and that didn't work.
Two problems occurred: first, new workbooks did not adopt that .xlt
file as a template. Second, even when I pasted that code into a
workbook, it did not display the desired behavior. I am using Office
2003 with all updates. Any idea what could be the problem?

JN

Helmut Weber wrote:
This one works for me:

Sub auto_close()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub

Sub auto_open()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub

Joseph,
I guess you are trying to replace the default Excel workbook that is created
when you click FileNew, click the New icon.
I seems to remember (when using Excel2000) altering the files "Book.xls" and
Sheet.xls" so all new WBs and WSs create incorporate these changes. However,
now I'm using Excel2002, I do not find these files on my system, so can't
tell you from which file(s) Excel generates these from, i.e. Excel
equivalent to Word's Normal.dot.

You can achieve the same result in code with Application level Events though
www.cpearson.com/excel/AppEvent.htm

NickHK

"Joseph N." wrote in message
ups.com...
Thanks Helmut. However, that code does not work for me.

I pasted it into the VB editor area of a workbook, making sure that it
was the workbook itself and not a template or add-in to which I was
adding the code. I digitally signed it with an accepted certificate,
since my macro security is set to "high," then I saved the workbook as
book.xlt . Following Microsoft's instructions, I first saved it in the
location of my templates, but that didn't work. Then I tried saving it
in the program's folder for startup templates, and that didn't work.
Two problems occurred: first, new workbooks did not adopt that .xlt
file as a template. Second, even when I pasted that code into a
workbook, it did not display the desired behavior. I am using Office
2003 with all updates. Any idea what could be the problem?

JN

Helmut Weber wrote:
This one works for me:

Sub auto_close()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub

Sub auto_open()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub

Joesph

Open a new workbook. Customize your as you wish.

FileSave As Type: scroll down to Excel Template(*.XLT) and select. Name your
workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for FileNew or the Toolbar button FileNew

Do not use FileNew...Blank Workbook or you will get the Excel default workbook.

NOTE: Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default InsertSheet.

More can be found on this in Help under "templates"(no quotes).


Gord Dibben Excel MVP


On Mon, 27 Nov 2006 10:45:10 +0800, "NickHK" wrote:

Joseph,
I guess you are trying to replace the default Excel workbook that is created
when you click FileNew, click the New icon.
I seems to remember (when using Excel2000) altering the files "Book.xls" and
Sheet.xls" so all new WBs and WSs create incorporate these changes. However,
now I'm using Excel2002, I do not find these files on my system, so can't
tell you from which file(s) Excel generates these from, i.e. Excel
equivalent to Word's Normal.dot.

You can achieve the same result in code with Application level Events though
www.cpearson.com/excel/AppEvent.htm

NickHK

"Joseph N." wrote in message
oups.com...
Thanks Helmut. However, that code does not work for me.

I pasted it into the VB editor area of a workbook, making sure that it
was the workbook itself and not a template or add-in to which I was
adding the code. I digitally signed it with an accepted certificate,
since my macro security is set to "high," then I saved the workbook as
book.xlt . Following Microsoft's instructions, I first saved it in the
location of my templates, but that didn't work. Then I tried saving it
in the program's folder for startup templates, and that didn't work.
Two problems occurred: first, new workbooks did not adopt that .xlt
file as a template. Second, even when I pasted that code into a
workbook, it did not display the desired behavior. I am using Office
2003 with all updates. Any idea what could be the problem?

JN

Helmut Weber wrote:
This one works for me:

Sub auto_close()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub

Sub auto_open()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub




Gord Dibben MS Excel MVP

That's what I remembered, but as I do not currently have such files on my
system, where is Excel retrieving these default files from ?

NickHK

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Joesph

Open a new workbook. Customize your as you wish.

FileSave As Type: scroll down to Excel Template(*.XLT) and select. Name
your
workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application
Data\Microsoft\Excel\XLSTART

This will be the default workbook for FileNew or the Toolbar button
FileNew

Do not use FileNew...Blank Workbook or you will get the Excel default
workbook.

NOTE: Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet. Customize
as
you wish then save this as SHEET.XLT in XLSTART folder also. It now
becomes
the default InsertSheet.

More can be found on this in Help under "templates"(no quotes).


Gord Dibben Excel MVP


On Mon, 27 Nov 2006 10:45:10 +0800, "NickHK"
wrote:

Joseph,
I guess you are trying to replace the default Excel workbook that is
created
when you click FileNew, click the New icon.
I seems to remember (when using Excel2000) altering the files "Book.xls"
and
Sheet.xls" so all new WBs and WSs create incorporate these changes.
However,
now I'm using Excel2002, I do not find these files on my system, so can't
tell you from which file(s) Excel generates these from, i.e. Excel
equivalent to Word's Normal.dot.

You can achieve the same result in code with Application level Events
though
www.cpearson.com/excel/AppEvent.htm

NickHK

"Joseph N." wrote in message
oups.com...
Thanks Helmut. However, that code does not work for me.

I pasted it into the VB editor area of a workbook, making sure that it
was the workbook itself and not a template or add-in to which I was
adding the code. I digitally signed it with an accepted certificate,
since my macro security is set to "high," then I saved the workbook as
book.xlt . Following Microsoft's instructions, I first saved it in the
location of my templates, but that didn't work. Then I tried saving it
in the program's folder for startup templates, and that didn't work.
Two problems occurred: first, new workbooks did not adopt that .xlt
file as a template. Second, even when I pasted that code into a
workbook, it did not display the desired behavior. I am using Office
2003 with all updates. Any idea what could be the problem?

JN

Helmut Weber wrote:
This one works for me:

Sub auto_close()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub

Sub auto_open()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub




Gord Dibben MS Excel MVP

Helmut's code worked fine for me (xl2003, no signatures, macros set to low).

Make sure you updated your only copy of book.xlt.

Excel is pretty smart. If you upgraded over a previous version, you'll find
that excel can still find the previous XLStart folder. And if there's a
book.xlt workbook template in that folder, you may be updating the wrong
version.

I'd spend a little time making sure that there are files in only one XLStart
folder. And I'd use the location that excel expects.

You can use this to technique to find that folder.

Open the VBE and hit ctrl-g and type this and hit enter:
?application.startuppath

For me with xl2003 and winXP (home), I get this:
C:\Documents and Settings\(username)\Application Data\Microsoft\Excel\XLSTART

I'd close excel and delete (or move for backup purposes only) all the files I
find in other XLStart folders and only use this XLStart folder.

Then make sure you have Book.xlt in that XLStart folder.

I have the following files in my XLStart folder:
personal.xla
book.xlt
sheet.xlt

Then when you click on the New icon on the standard toolbar, excel will use that
book.xlt workbook to create a new workbook. It won't (and shouldn't!) open the
file as a template. It won't create a workbook with .xlt as an extension--in
fact, there shouldn't be any extension on that new workbook at all.

The name of the workbook will be book1 (or book2 or book3...). It won't get the
extension until you save the file (as a normal workbook???).




"Joseph N." wrote:

Thanks Helmut. However, that code does not work for me.

I pasted it into the VB editor area of a workbook, making sure that it
was the workbook itself and not a template or add-in to which I was
adding the code. I digitally signed it with an accepted certificate,
since my macro security is set to "high," then I saved the workbook as
book.xlt . Following Microsoft's instructions, I first saved it in the
location of my templates, but that didn't work. Then I tried saving it
in the program's folder for startup templates, and that didn't work.
Two problems occurred: first, new workbooks did not adopt that .xlt
file as a template. Second, even when I pasted that code into a
workbook, it did not display the desired behavior. I am using Office
2003 with all updates. Any idea what could be the problem?

JN

Helmut Weber wrote:
This one works for me:

Sub auto_close()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub

Sub auto_open()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub


--

Dave Peterson