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

  • Thread starter Thread starter Chip Orange
  • Start date Start date
C

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
 
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
 
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
 
Hi Chip,

Surely the approach and code be same in Word, or have you done something
different.

Yes you can place the code in your Personal.xls. In one of the open events
just call SetNewFileEvents, eg

Sub auto_open()
SetNewFileEvents
end sub

Regards,
Peter T

PS Forgot to mention last time WithEvents CommandBarButton is not supported
in Office-97, so can't trap menu button clicks in that version.



I forget I forgot to mention last time the approach will not work in Office
97)
 
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
 
In Word, I would have done this simply by naming a procedure FileNew(), so
this does look different to me.
Click to expand...

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
 
Back
Top