Opening properties box

[Steveb]

Hi

Is there anyway of having the properties dialog box open up automatically
when starting a blank spreadsheet? The box I refer to is accessed nomally
from the File / Properties on the menubar.

Thanks

Steve

 

[Izar Arcturus]

Select Options in the Tools dropdown menu in the toolbar
at the top of the Excel window, in the Options dialog box
that appears click in the check box by "Prompt for
workbook properties".

The way this works is, workbook properties actually only
become properties after they have been entered and saved.
So, MS has set this prompt to only appear when a new file
is saved for the first time.

This means that after setting this in the Options, when
you open a new file save it immediately and the Properties
dialog box will open automatically after you have typed in
the name of the file and clicked on the Save button.

I don't know how to show the Properties dialog box using
VBA yet.

-IA

 

[Bill Manville]

Is there anyway of having the properties dialog box open up automatically
when starting a blank spreadsheet?

The code is easy:
Application.Dialogs(xlDialogProperties).Show

The question is how to make it run.

You could put it in Workbook_Open in a workbook template BOOK.XLT in your
XLSTART directory - but then you will get the macro virus warning when you
create a new workbook. Also you would need to arrange for it only to run
on first open

If ThisWorkbook.Path = "" Then
Application.Dialogs(xlDialogProperties).Show
End If

Better would be to make an application-level event handler in your
PERSONAL.XLS detect a new workbook.
In Personal.xls (or some other workbook in your XLSTART directory):
- Insert / Class Module
- name it clsApp (for example)
- paste in this code:

Public WithEvents oXL As Excel.Application

Private Sub oXL_NewWorkbook(ByVal Wb As Excel.Workbook)
If Wb.Path = "" Then
Application.Dialogs(xlDialogProperties).Show
End If
End Sub

- Insert / Module
- paste in the following code:


Dim clsXL As New clsApp

Sub Autpen()
Set clsXL.oXL = Application
End Sub

Sub Auto_Close()
Set clsXL = Nothing
End Sub

- save the workbook
- run Autpen
- create a new workbook (to test it out).






Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup