Trapping Sheet selection
in [Visual Basic]
|
Prev: Help with Zebra ZM400
Next: HELP CLR20r3 need help to debug crash of exe startup at remote installation
From: avi on 11 Aug 2010 11:13 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 11 Aug 2010 12:23 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 11 Aug 2010 18:47 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 13 Aug 2010 05:40
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 |