If it were just one workbook, you could use code like this:
Option Explicit
Sub auto_open()
Application.Dialogs(xlDialogProperties).Show
End Sub
but this only affects that one workbook. (One of the differences between word
and excel.)
But you could do it with an application event.
Put your hat on!
Start a new workbook
Alt-F11 to get the VBE (where macros live)
hit ctrl-R to see the project explorer (a lot like windows explorer)
select your project (book1) VBAProject
right click and select Insert|Class Module
(Let it default to its Class1 name)
Paste this in that code window to the right:
Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
Application.Dialogs(xlDialogProperties).Show
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
Application.Dialogs(xlDialogProperties).Show
End Sub
Now doubleclick on the ThisWorkbook object of this same project.
(you might have to expand all the levels to see it)
Paste this in the code window to the right.
Option Explicit
Dim myPropDialog As Class1
Private Sub Workbook_Open()
Set myPropDialog = New Class1
Set myPropDialog.xlApp = Application
End Sub
Private Sub Workbook_Close()
Set myPropDialog.xlApp = Nothing
End Sub
Now back to excel and save your workbook--but save it as an addin and store it
in your XLStart folder.
By saving it as an addin, it'll be invisible to you when you're in excel. By
storing it in XLStart, your addin will load whenever you start excel.
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm