ExecuteExcel4Macro to run on other page

M

Makelei

Hi,
XP and 2003.
I should get this to work:
Private Sub Worksheet_Deactivate()
....
For i = 1 To 14
ExecuteExcel4Macro "Sheet1.SHOW.DETAIL(1," & i & ",false)"
Next i
.....
end sub

My problem is here:
ExecuteExcel4Macro "Sheet1.SHOW.DETAIL(1," & i & ",false)"
That Sheet1 is not functioning when it is in Private Sub
Worksheet_Deactivate(). It tries to run it in the activated sheet. Is it and
how is it possible to get it to run in Sheet1?

Thanks in advance
MakeLei
 
P

Peter T

Private Sub Worksheet_Deactivate()
Dim sht As Object
On Error GoTo errExit
Application.EnableEvents = False
Set sht = ActiveSheet
Application.ScreenUpdating = False
Me.Activate
'your code here

ActiveSheet.Range("a1") = Me.Name
sht.Activate
errExit:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

I haven't looked to see if there isn't a VBA equivalent of your XL4 macro,
if so this would not be an issue.

Regards,
Peter T
 
M

Mike Fogleman

Try putting the code in ThisWorkbook module not a sheet module -
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

End Sub

Mike F
 
J

Jim Rech

ExecuteExcel4Macro "Sheet1.SHOW.DETAIL(1," & i & ",false)"

That's not valid syntax. You're melding VB concepts (the "Sheet1") to XL4
macro code. XL4 code for the most part only works on the active sheet. So
make Sheet1 active and delete that reference from the code.
 
P

Peter T

Jim Rech said:
That's not valid syntax. You're melding VB concepts (the "Sheet1") to XL4
macro code.
Click to expand...

That's what I thought but curiously it doesn't error.
XL4 code for the most part only works on the active sheet. So make Sheet1
active and delete that reference from the code.
Click to expand...

I posted an example of how to do that within the sheet's deactivate event

Regards,
Peter T
 
J

Jim Rech

That's what I thought but curiously it doesn't error.

Looks like if Excel doesn't see it as an XL4 command it just lets it pass.
This didn't error either:

Application.ExecuteExcel4Macro "NotSubWithThisName"
 
Top