Excel Macro Advise.....To Open The Template Dialog.....

[Guest]

I am using Excel 2002 and 2003 and get diferent results from this macro.
Using a macro to open the Templates Dialog.....Advise Please.

Sub AutoExec( )

Application.Dialogs(xlDialogNew).Show

End Sub

When Running from within the VBE of Excel 2003 it will work ...Open the
Dialog Box.
but it will not run on startup as expected of an AutoExec.

When Running on Excel 2002, it doesn't appear to run al all fron within the
VBE to test it or on Startup.
If I step through it in the VBE, nothing happens.

All suggestions would be appreciated. Thanks

 

[Brian Taylor]

Use workbook open event of the workbook:

Private Sub Workbook_Open()
Application.Dialogs(xlDialogNew).Show
End Sub

 

[Guest]

Hi Brian
Thanks for the reply.
I opened a new workbook.
Tools | Macros | VBE | View Code....I also have project explorer showing in
the left pane.
I have copied and pasted the code you supplied into a new workbook to test it.
When I click on the Run Triangle icon in the toolbar nothing happens. If I
step through it nothing happens.
Can you suggest what I might be doing wrong?

Please explain what the difference is between the code you have given me
and what I was using as posted in my first posting.
Your help is appreciated as I know I must be doing something silly wrong.

 

[Brian Taylor]

You have to make sure to put it in the workbook code section. Go into
the VBE and and instead of putting the code in a regular module, double
click on ThisWorkbook in the project explorer. This should open the
code section for the workbook. Paste the code in that section.

An even better method is, once you open the workbook code section, if
you click on the drop down that says general and change that to
workbook then you will get a list of event triggers in the second drop
down. You can select open from that list and it will create the
workbook_open event sub header for you. Let me know if my explanation
makes sense.

 

[Guest]

You have to make sure to put it in the workbook code section. Go into
the VBE and and instead of putting the code in a regular module, double
click on ThisWorkbook in the project explorer. This should open the
code section for the workbook. Paste the code in that section.

An even better method is, once you open the workbook code section, if
you click on the drop down that says general and change that to
workbook then you will get a list of event triggers in the second drop
down. You can select open from that list and it will create the
workbook_open event sub header for you. Let me know if my explanation
makes sense.

 

[Guest]

Hi Brian
I followed your instruction to insert the code in this workbook, selected
Workbook from the General Drop down and Open from the Event dropdown.
Then I inserted the code: Application.Dialogs(xlDialogNew).Show betwen the
open heading and End Sub.
When I run it....nothing happens.....did this work for you?

What I am trying to do is get the Template dialog box to open when I open
excel....I can then select the appropriate template I want to use from the
dialog box.

I can get similar code to work for Word 2002 / 2003 as shown below.
Sub AutoExec()
'
' AutoExec Macro to open the template Dialogue Box
' Macro created 25/02/2006 by Dermot Hayes
'
Dialogs(wdDialogFileNew).Show

End Sub

Any other help would be appreciated.

 

[Brian Taylor]

Dermot,

This code worked for me both when I stepped through it one line at a
time and also when I opened the workbook. Try testing the
workbook_open event by putting something really simple in there like
msgbox "Hello!" and see if it works. Let's find out if it is the
workbook open event or the dialogs call that is failing. I am also
working in excel 2000, but I don't think Microsoft introduced another
method for opening dialogs in 2002.

 

[Guest]

Hi Brian
Thanks again for the reply.
I did as you suggested and tried the following code...........
Private Sub Workbook_Open()
MsgBox "Hello!"
End Sub

It worked fine both stepping through and on opening the
workbook.....producing the "Hello" message box.

To Quote You
"Let's find out if it is the workbook open event or the dialogs call that
is failing".
Question
So how do I now troubleshoot the "Open Event" and "Dialogs Call" to
determine where the problem might lie.
Thanks again Brian
Dermot

 

[Brian Taylor]

That means the problem is not with the workbook_open event and is
definitely with the line Application.Dialogs(xlDialogNew).Show.

I don't know what else to tell you on why the line isn't working. Try
putting that line in a normal procedure in a module and see if it runs:

sub MyTest
Application.Dialogs(xlDialogNew).Show
end sub

 

[Guest]

Hi Brian
I am wondering if I have been hit by a virus and my Excel installation
affected. Although I can't say if this mactro ever work because I have never
tried it before.
Should should the following code examples work also?
Private Sub Workbook_Open()
Application.Dialogs(xlDialogNew).Show
End Sub

Question
I changed the heading to test Auto_Open Event.......that didn't work
I also change the heading to test AutoExec........that didn't work.

Could you explain to me the difference between Open, Auto_Open and AutoExec

Here are the code examples I mentioned above.

Private Sub AutoExec
MsgBox "Hello!"
End Sub

Or

Private Sub Auto_Open
MsgBox "Hello!"
End Sub

I thought Auto _Open and AutoExec should have the same result as the Open
event macro?

Thanks
Dermot

 

[Brian Taylor]

AutoExec was the excel 95 solution for having a macro run automatically
when a workbook opened. Excel 97 and Excel 2000 can handle both
AutoExec and Workbook_Open, but I think that any later versions of
excel only use Workbook_Open.

I found this in the Microsoft knowledge base that may be the problem
you are refering to with Application.Dialogs(xlDialogNew).show:

http://support.microsoft.com/default.aspx?scid=kb;en-us;885236

 

[Dave Peterson]

Auto_open is used in excel.

AutoExec isn't (well, it can be used just like any other valid procedure name
that the user wants--but it doesn't mean anything special to excel).

And Auto_Open would be placed in a General module--not under ThisWorkbook.

And all versions of excel still support auto_open.

 

[Brian Taylor]

Thanks for clarifying Dave. Is there any valid reason why you would
want to use an Auto_Open subprocedure in a standard module instead of
the Workbook_Open event in ThisWorkbook? It seems a little redundant
to me to have both.

 

[Dave Peterson]

I think that they're pretty much a matter of personal preference.

Sometimes, it's easier to explain where to put code if you only have to specify
a single location/module.

But there are technical differences--which runs first if you have both, for
example.

If you open the workbook using code, you have to run the auto_open procedure
yourself if you want it to run.

 

[Guest]

Thanks both Dave and Brian for the last few posts.....
I am still in the process of checking out the MS KB fix this is interesting
stuff...thanks.

I know this is slightly off the original topic but could you explain the
difference between "Thisworkbook" code and a standard module?
Thanks

 

[Dave Peterson]

Excel has some special class modules that are used for special stuff--it has
class modules for each worksheet and a class module for ThisWorkbook.

For the most part, I figure that the code in the ThisWorkbook module should be
the events that are available from the dropdown list at the top of the code
window when the ThisWorkbook module is selected.

Just a few (from xl2003):
Private Sub Workbook_Activate()
Private Sub Workbook_AddinInstall()
Private Sub Workbook_AfterXmlExport(ByVal Map As XmlMap, _
ByVal Url As String, ByVal Result As XlXmlExportResult)Private
Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_Open()

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm
and
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

General modules are made for general stuff <vbg>--almost everything else. If
you need to create a UDF to do some fancy smancy thing in your worksheet, that
code goes here.

If you create a macro that is run via a shortcut key (or tools|macro|macros...),
the code goes here.

If you need to assign a macro to a shape (from the Drawing toolbar or from the
Forms toolbar), the code goes here.

(But the objects from the control toolbox toolbar go behind the worksheet that
holds that object.)

 

[Brian Taylor]

ThisWorkbook is an object with code attached to it (just like
worksheets, userforms, buttons, combo boxes, etc). All objects have
events that they look for before running (single clicks, double clicks,
button pushes, workbook opens, sheet activates, etc.). This is the
only place to kick off your code (besides running the macro through
tools - macros - run or in VBE). So all of your code originally starts
from an event off of an object.

A module is just a place to hold procedures and functions (with a
procedure being code that performs some action and a function being
code that returns a value to whatever called it). But any code within
a module has to be called from an object (or from another module that
was originally called by an object). The benefit of putting code into
modules is that it can be shared by multiple objects or with other
modules. For example if you had a procedure that updated sales data on
a workbook, you could have that procedure run when called by
workbook_open event in the ThisWorkBook object or when you pushed a
button on a worksheet:

On ThisWorkBook:

sub workbook_open
UpdateMyStuff
end sub

On Sheet1:

sub Button1_Click
UpdateMyStuff
end sub

In a module:

sub UpdateMyStuff
Lot's of complicated code here
End Sub

You don't have to put the code both in the ThisWorkBook and the
Button''s code sections. You only need it in a standard module.

That keeps the code concise, easier to debug or change, and faster.
Hope that helps.

Brian

 

[Guest]

Brian I have read the following from the link above.
I can't find a download for the fix.....have you any suggestions...I
followed the lik to support but couldn't see anything email contact that I
could use as I am using Excel 2002. Have you any suggestions?
Thanks

 

[Brian Taylor]

It says in the article that you are supposed to contact them for the
service pack 3 hotfix. This is the link they provided:

http://support.microsoft.com/contactus/?ws=spport