From: avi on
hello,

I want to execute (in a vb6 exe) a procedure when the user selects
another sheet in Excel

In a Class module I use
Private WithEvents mWk As Excel.Workbook
Sub mWk_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "dsfdsfDSF"
End Sub
Private Sub mWk_SheetActivate(ByVal Sh As Object)
MsgBox "dsfdsfDSF"
End Sub

But so far, the event does not fire (although it does in VBA)

Please help
Avi
From: GS on
avi explained on 8/11/2010 :
> hello,
>
> I want to execute (in a vb6 exe) a procedure when the user selects
> another sheet in Excel
>
> In a Class module I use
> Private WithEvents mWk As Excel.Workbook
> Sub mWk_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> MsgBox "dsfdsfDSF"
> End Sub
> Private Sub mWk_SheetActivate(ByVal Sh As Object)
> MsgBox "dsfdsfDSF"
> End Sub
>
> But so far, the event does not fire (although it does in VBA)
>
> Please help
> Avi

Try it using the Excel.Application object that you're automating,
instead of the Excel.Workbook object. (Same event name:
appXL_SheetActivate)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


From: GS on
GS formulated the question :
> avi explained on 8/11/2010 :
>> hello,
>>
>> I want to execute (in a vb6 exe) a procedure when the user selects
>> another sheet in Excel
>>
>> In a Class module I use
>> Private WithEvents mWk As Excel.Workbook
>> Sub mWk_SheetChange(ByVal Sh As Object, ByVal Target As Range)
>> MsgBox "dsfdsfDSF"
>> End Sub
>> Private Sub mWk_SheetActivate(ByVal Sh As Object)
>> MsgBox "dsfdsfDSF"
>> End Sub
>>
>> But so far, the event does not fire (although it does in VBA)
>>
>> Please help
>> Avi
>
> Try it using the Excel.Application object that you're automating, instead of
> the Excel.Workbook object. (Same event name: appXL_SheetActivate)

I forgot to mention that it requires early binding to work because you
will need a ref to the Excel n Object Library, where n is the earliest
version of Excel you expect your app to be used with.

Also, your WithEvents variable must be declared as Excel.Application
and must be 'Set' to ref your instance of Excel.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


From: Peter T on
Your code should work fine. However as written you'll need an additional
procedure or Property to assign 'Private' mWk to your particular workbook,
which I assume you have but didn't post. Alternatively it might be easier to
change

Private WithEvents mWk As Excel.Workbook
to
Public WithEvents mWk As Excel.Workbook

and when you instanciate the class do something like this -

Set mCls = new Class1
Set mCls.mWk = myExcelWorkbook ' eg xlApp.ActiveWorkbook

If/having declared mWK as public you might want to rename it more
appropriately

Regards,
Peter T



"avi" <aviben(a)bezeqint.net.il> wrote in message
news:2950745c-a730-4305-bac8-b7d1c70ac5db(a)k10g2000yqa.googlegroups.com...
> hello,
>
> I want to execute (in a vb6 exe) a procedure when the user selects
> another sheet in Excel
>
> In a Class module I use
> Private WithEvents mWk As Excel.Workbook
> Sub mWk_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> MsgBox "dsfdsfDSF"
> End Sub
> Private Sub mWk_SheetActivate(ByVal Sh As Object)
> MsgBox "dsfdsfDSF"
> End Sub
>
> But so far, the event does not fire (although it does in VBA)
>
> Please help
> Avi