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

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

--
Regards,
Tom Ogilvy


"avi" wrote:

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

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

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.
--
Regards,
Tom Ogilvy



"avi" wrote:

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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"avi" wrote in message
oups.com...
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

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

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
_______


"avi" wrote in message
ups.com...
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

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

On 30 août, 15:25, "Jon Peltier"
wrote:
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
_______

"avi" wrote in message

ups.com...



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- Masquer le texte des messages précédents -

- 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

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
_______


"avi" wrote in message
ups.com...
On 30 août, 15:25, "Jon Peltier"
wrote:
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
_______

"avi" wrote in message

ups.com...



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- Masquer le texte des messages précédents -

- 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