deleting OLE control from inactive worksheet

S.Br.

Board Regular
Joined
Oct 5, 2012
Messages
74
Office Version
365, 2016, 2013, 2011, 2010, 2007
Dear Mr.Excel, Happy Holidays;
In my Add-In I monitor the SheetActivate event, and create an OLE control by calling active sheet's .OLEObjects.Add() method.
When the sheet de-activates its SheetDeactivate event is fired and I need to delete the control by calling its .Delete() member.
Excel is very unhappy with this approach because it is not designed to work with inactive worksheets: the SheetDeactivate event
is called when the worksheet is inactive. How do I get around this design? How do I delete an OLEObject from inactive worksheet?
Many thanks in advance.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
Don't use ActiveSheet, use the name or codename of the sheet that conatains the oleobject control ... Something like this :
Code:
Private Sub Worksheet_Deactivate()
 [COLOR=#ff0000][B]Sheet1[/B][/COLOR].OLEObjects("[COLOR=#ff0000][B]CommandButton1[/B][/COLOR]").Delete
End Sub
Change the names in red to suit.
 
Last edited:

S.Br.

Board Regular
Joined
Oct 5, 2012
Messages
74
Office Version
365, 2016, 2013, 2011, 2010, 2007
... looks like there's no way; too bad.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,703
Messages
5,470,295
Members
406,688
Latest member
Trevor Steward

This Week's Hot Topics

Top