Taking over OLEObjects... Possible ?!

[Alex T]

Folks

Any chance to have the following code running ?

1. --- Class module

Option Explicit

Public WithEvents myObj As Excel.OLEObject

Private Sub myObj_GotFocus()

MsgBox "focus"

End Sub

2. --- Module

Option Explicit

Dim myButtons() As New clsMyOLEObj


Sub doStuff()

Dim x As OLEObject
Dim numItems As Integer


numItems = 0
For Each x In ActiveSheet.OLEObjects

numItems = numItems + 1
ReDim Preserve myButtons(1 To numItems)
Set myButtons(numItems).myObj = x

Next x

End Sub

The idea is to "take over" the OLEObjects and have one centralized
processing.

I'm doing (as per John Walkenbach
http://j-walk.com/ss/excel/tips/tip44.htm ) similar things with
userforms controls and it works well. However my code above fails when
assigning to myButtons(numItems).

Any idea ?


Thanks & regards

--AlexT

 

[Bob Phillips]

To quote a previous reply from Rob Bovey, ... You won't be able to trap the
GotFocus or LostFocus events if you do so outside of the worksheet's code
module. This is because those events are provided by the worksheet
container.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Alex T]

To quote a previous reply from Rob Bovey, ... You won't be

able to trap the GotFocus or LostFocus events if you do so
outside of the worksheet's code module. This is because
those events are provided by the worksheet container.

So the conclusion is that there is no hope to create a generic event
handler for OLE objects... Too bad...

Thanks anyway

Alex

 

[Jamie Collins]

In case it isn't already obvious, you could do this:

' --- Class module named CButton
Option Explicit

Private WithEvents m_Button As MSForms.CommandButton

Public Function Init( _
ByVal Button As MSForms.CommandButton _
) As Boolean
Set m_Button = Button
Init = True
End Function

Private Sub m_Button_Click()
MsgBox "Click"
End Sub

' --- ThisWorkbook code module
Option Explicit

Private m_Buttons As Collection

Private Sub Workbook_Open()

Dim x As Excel.OLEObject
Dim oButton As CButton

Set m_Buttons = New Collection

With Sheet1
For Each x In .OLEObjects
If TypeOf x.Object Is MSForms.CommandButton Then
Set oButton = New CButton
oButton.Init x.Object
m_Buttons.Add oButton, x.Object.Name
End If
Next x

End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set m_Buttons = Nothing
End Sub

Note that if controls are being added/removed from you workbook, all
variables in the VBA project will be reset, so you will have to find a
way of re-running the code to take account of any changes. If you are
not addeding/removing controls, there's no need to loop through the
OLEObjects e.g.

With Sheet1
AddButton .CommandButton1
AddButton .CommandButton2
AddButton .CommandButton3
End With

End Sub

Private Function AddButton( _
ByVal MSFormsCommandButton As MSForms.CommandButton _
) As Boolean

Dim oButton As CButton
Set oButton = New CButton
oButton.Init MSFormsCommandButton
m_Buttons.Add oButton, MSFormsCommandButton.Name

End Function

Jamie.

--

 

[Jamie Collins]

Typo! Should be:

m_Buttons.Add oButton, x.Name

Further note: If you have controls other than CommandButtons, you
could accommodate with a respective WithEvents control for each type,
either within the same class or a different class for each type.

Jamie

--