DoubleClick on embedded object(shape)

[avi]

Hello,

I embed in the active sheet many objects (of a certain ActiveX type)
that have a doubleClick event. I want to use the doubleClick to
trigger a userform taht show the the clicked object parameters.

The problem is that in order to double click a particular object, i
have to know the specific name or index of the object in order to use
a procedure Sub SpecificName_DblClick()

The number of embedded object (shapes) could vary and is not known in
advance

I'll be glad if someone could help me with what it looks quite a
tricky problem

Thanks a lot
Avi

 

[Tom Ogilvy]

Can you right click on an object and select view code?

 

[avi]

No!

There is is a property item whe right clicking, but it triggers a
built-in form with no mention of the object name

Avi

 

[Tom Ogilvy]

Try this:
Sub aBC()
for each obj in ActiveSheet.OleObjects
obj.Select
msgbox obj.Object.Name & " - " & obj.Name
Next
End Sub


if obj.Select causes a problem, then comment it out and try it.

Also, if you select an object you should see its name in the namebox on the
Formula bar.

 

[Jon Peltier]

If you don't know what's present at design time, you'll have to write a
class module to handle the event, and instantiate an instance of the class
for each object that's present at run time. This might help get you started:

http://www.j-walk.com/ss/excel/tips/tip44.htm

It says UserForm buttons, but it should also work with worksheet controls.
- Jon

 

[avi]

Thanks for you help, but still don't know how to solve completely.
I have created a collection of the objects (a Gauge object activeX)
that work fine, but do not know how to associate the event DblClick
with its members. The ActiveX itself has this Event

Dim MyCollection As New Collection
Dim Gauges() As New Class1
Public i As Integer
Sub CollectionGauges()
Dim GaugeCount As Integer
Dim Gauge As OLEObject
GaugeCount = 0

For Each Gauge In ActiveSheet.OLEObjects
MyCollection.Add Gauge

GaugeCount = GaugeCount + 1
ReDim Preserve Gauges(1 To GaugeCount)
Set Gauges(GaugeCount).GaugesGroup = Gauge 'This line
return an error
Next

MsgBox ActiveSheet.OLEObjects(8).Object.Value 'This indeed
return the Value associate witj the ActiveX gauge

End Sub

The Class1 code is:

I have created Class1 but get an error message. I guess that I have to
create a Class and associate a DblClick event whith it but do not know
what kind of class to create. Should it be a collection?

Public WithEvents GaugesGroup As OLEObject
Private Sub GaugesGroup_DblClick()
MsgBox "Hello from " & GaugesGroup.Name
End Sub

Any guidance would help me a lot
Thanks again
Avi

 

[Jon Peltier]

Try this:

Set Gauges(GaugeCount).GaugesGroup = Gauge.Object

- Jon

 

[avi]

Hello, Jon

It works!!!! many thanks!!

i've made also a slight change to the class, otherwise i get a Type
mismatch

Public WithEvents GaugesGroup As GaugeObject
Private Sub GaugesGroup_DblClick()
MsgBox "Hello from " & GaugesGroup.Name
End Sub


Avi

 

[avi]

Try this:

Set Gauges(GaugeCount).GaugesGroup = Gauge.Object

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______















- Afficher le texte des messages précédents -

Hello, Jon

Could you help me further?

When i DbleClick the object, the msgBox dispays its name attribute

But how i could use this same name in a UserForm that i open as a part
of the dbleClickEvent?

I've added a Public statement in the Class, but nothing happens

Thanks alot
Avi

 

[Jon Peltier]

Ah, now you want to do something practical!

In the userform code module, add a module-level variable in the Declarations
section:

Private msControlName As String

Also add a property to the userform:

Public Property Let ControlName(sControlName As String)
msControlName = sControlName
' now make whatever use of the value that you need, for example
me.lblControlName.Caption = msControlName
End Property

In the DoubleClick event procedure in the event class module, load the form,
pass the control name to the form, then show the form:

Public WithEvents GaugesGroup As OLEObject
Private Sub GaugesGroup_DblClick()
Load MyUserForm
With MyUserForm
.ControlName = GaugesGroup.Name
.Show
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Try this:

Set Gauges(GaugeCount).GaugesGroup = Gauge.Object

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______















- Afficher le texte des messages précédents -

Hello, Jon

Could you help me further?

When i DbleClick the object, the msgBox dispays its name attribute

But how i could use this same name in a UserForm that i open as a part
of the dbleClickEvent?

I've added a Public statement in the Class, but nothing happens

Thanks alot
Avi

 

[avi]

You are simply... great!

Thanks a lot
Avi

 

[avi]

Hi Jon,

It seems that i try to push the limits of Excel ActiveX capabilities.

Now, when i create a new component in the active sheet and try to
activate the class so that the DbleClick event is recognized, the
event is actually not recognized for the last component created.

It seems to relate to the msg i get when running with F8 just after
the creation line " can't execute code in break mode"

I've found that switching from design mode on and off manually, then
activating the class, does the job but I look naturally to make the
switch programatically. Is it possible?

Could you think about some way to get around the problem?

Thanks a lot again
Avi

 

[Jon Peltier]

I did not encounter this issue when I did a simple trial. I don't know what
causes it for you.

- Jon