jacknsa

Converting some VBA to VSTO. Trying to wire up worksheet event handler at run time - using VB. Works fine for most events (Deactivate, BeforeDoubleClick, etc) but can't assign ActivateEvent handler.

What am I missing



Re: Visual Studio Tools for Office Excel worksheet ActivateEvent

Adamus Turner

...an answer :)

Can you post some code please

Adamus






Re: Visual Studio Tools for Office Excel worksheet ActivateEvent

Steve Hansen

 

You should be able to add a simple event handler such as this (add to Sheet1.vb for example).

Private Sub Sheet1_ActivateEvent() Handles Me.ActivateEvent

   MessageBox.Show("Activated")

End Sub

If that doesn't work - you'll need to provide us with more details on what you're trying to do.

Regards,

Steve






Re: Visual Studio Tools for Office Excel worksheet ActivateEvent

Adamus Turner

I'm still confused on what you're trying to accomplish.

Generally you would call the macro inside Excel if it's a template used to generate a report.

...or if

The code (VB.net) is dumping something into excel, why do you need to access or execute/activate events in a new blank workbook

Adamus






Re: Visual Studio Tools for Office Excel worksheet ActivateEvent

jacknsa

I'm building the worksheet at run-time, not design-time. Following code works fine:

AddHandler _mySheet.BeforeDoubleClick, AddressOf testevent

AddHandler _mySheet.Deactivate, AddressOf testevent2

Intellisense has no event for ActivateEvent and if I enter it manually I get an error "ActivateEvent is not an event of Microsoft.Office.Interop.Excel.Worksheet"





Re: Visual Studio Tools for Office Excel worksheet ActivateEvent

Steve Hansen

Ahh - my bad - missed the "run time" when reading your original post . Anyway - you're close - instead of ActivateEvent it should just be Activate. For example:

AddHandler _mySheet.Activate, AddressOf testevent3

Regards,

Steve






Re: Visual Studio Tools for Office Excel worksheet ActivateEvent

jacknsa

Thanks. Works great. Still can't figure out why Intellisense shows all the other options but not that one - oh well.



Re: Visual Studio Tools for Office Excel worksheet ActivateEvent

Steve Hansen

Glad to help. Regarding the Intellisense - this is because Activate is the name of a method and an event on the Worksheet object. Due to this collision, it doesn't appear as an event in Intellisense - you can only see the Activate method. The Activate event is actually specified on a different interface - DocEvents_Event.

Regards,

Steve






Re: Visual Studio Tools for Office Excel worksheet ActivateEvent

jacknsa

Since you've been so helpful maybe you can help with another riddle I've been wrestling with. Have looked everywhere but can't seem to find the VSTO equivalent to the VBA "ActiveWindow.Zoom = 75" command to change view size to 75%, for example. This used to work:

Dim oXL As Excel.Application

oXL = CreateObject("Excel.Application")

...

ws.Columns("A:L").Select()

oXL.ActiveWindow.Zoom = True

I can't find the VSTO equivalent. Any ideas





Re: Visual Studio Tools for Office Excel worksheet ActivateEvent

Steve Hansen

How are you trying to call it You should be able to use virtually the same thing. For example, within Sheet1.vb (would work within ThisWorkbook.vb too), you could use this to zoom to 75%:

Me.Application.ActiveWindow.Zoom = 75

Regards,

Steve Hansen






Re: Visual Studio Tools for Office Excel worksheet ActivateEvent

jacknsa

Doh! It's the obvious ones that are the most humbling. Thanks.