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

I should have added this is Excel 2003.

thanks.

Chip


"Chip Orange" wrote in message
...
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


"Chip Orange" wrote in message
...
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

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


"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

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)
"Chip Orange" wrote in message
...
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


"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

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" <peter_t@discussions wrote in message
...
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)
"Chip Orange" wrote in message
...
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


"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

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

"Chip Orange" wrote in message
...
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" <peter_t@discussions wrote in message
...
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)
"Chip Orange" wrote in message
...
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


"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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