deleting OLE control from inactive worksheet

S.Br.

Board Regular
Joined
Oct 5, 2012
Messages
62
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.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,558
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:

Forum statistics

Threads
1,085,301
Messages
5,382,808
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top