intercepting the file | new command or the file | new menu choice

[Chip Orange]

I understand from a web page I found that Excel does not allow you to
intercept commands (such as the file new command) as does Word, is this
correct?

If so, could I modify the action of the file | new menu choice to run a
macro (using a macro run at startup)?


thanks.

Chip

 

[Chip Orange]

I should have added this is Excel 2003.

thanks.

Chip

 

[Peter T]

Add a class module and place the code below in a normal module & class
module as indicated.

'' in a normal module

Dim clsNewFile As Class1

Sub SetNewFileEvents()
Dim cbb As CommandBarButton

Set cbb = Application.CommandBars.FindControl(ID:=18)
' MsgBox cbb.Caption ' confirm got correct button

Set clsNewFile = New Class1
Set clsNewFile.pCbb = cbb

End Sub

' in a class module named "Class1"

Public WithEvents pCbb As CommandBarButton

Private Sub pCbb_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
If MsgBox("New file ?", vbYesNo) <> vbYes Then
CancelDefault = True
End If

End Sub

Run SetNewFileEvents, eg from an open event.

Later you may want to rename the class module to something more meaningful,
eg "clsNewFileEvnts"

Regards,
Peter T

 

[Chip Orange]

Thank you Peter! I've done something very similar in Word, intercepting
events, but I don't think I could have figured this one out in Excel.

I want this to happen for all my users, any reason why I should not place it
in personal.xls in the xlstart dir of their profile?

Thanks.

Chip

 

[Chip Orange]

Thanks again Peter.

I realized this looks different to me because in Word I've only used the
application object as a basis for a class with events. I don't recall being
able to trap any mouse events that way.

In Word, I would have done this simply by naming a procedure FileNew(), so
this does look different to me.

Chip

 

[Peter T]

In Word, I would have done this simply by naming a procedure FileNew(), so

this does look different to me.

I confess I was skeptical but indeed that works in Word (but not in Excel).
Actually it seems to intercept the new file command completely and prevents
creation of a new document. Curious!

FYI the event method I outlined for Excel also works in Word, it might give
you a little more control.

Regards,
Peter T