Class module question

[David]

The code below is from John Walkenbach's site (j-walk.com)
It makes easy work of handling multiple UserForm Buttons with one subroutine.
I've been trying to adapt it to work with multiple image controls on a
WORKSHEET rather than a UserForm. Any advice will be much appreciated


Class1 code:
Public WithEvents ButtonGroup As CommandButton

Private Sub ButtonGroup_Click()
MsgBox "Hello from " & ButtonGroup.Name
End Sub

Module1 code:
Dim Buttons() As New Class1

UserForm1 code:
Private Sub OKButton_Click()
Unload Me
End Sub

Sub ShowDialog()
Dim ButtonCount As Integer
Dim ctl As Control

' Create the Button objects
ButtonCount = 0
For Each ctl In UserForm1.Controls
If TypeName(ctl) = "CommandButton" Then
If ctl.Name <> "OKButton" Then 'Skip the OKButton
ButtonCount = ButtonCount + 1
ReDim Preserve Buttons(1 To ButtonCount)
Set Buttons(ButtonCount).ButtonGroup = ctl
End If
End If
Next ctl
UserForm1.Show
End Sub

 

[Peter T]

Hi David,

Drop a few Image controls onto the worksheet from the Controls Toolbox menu.
Try this demo and click the images.

' in Class1
Public WithEvents img As MSForms.Image
Dim sName As String
Dim id As Long

Public Property Let propName(nID As Long, s As String)
id = nID
sName = s
End Property

Private Sub img_Click()
MsgBox "My Name " & sName & vbCr & "My Id " & id
End Sub

' end Class1


' In a normal module
Dim maImages() As New Class1

Sub Setup()
Dim oOLE As OLEObject
Dim cntImage As Long

For Each oOLE In ActiveSheet.OLEObjects
If TypeOf oOLE.Object Is MSForms.Image Then
cntImage = cntImage + 1
ReDim Preserve maImages(1 To cntImage)
Set maImages(cntImage).img = oOLE.Object
maImages(cntImage).propName(cntImage) = oOLE.Name
End If
Next

End Sub

Sub Clearup()
On Error Resume Next ' error if the array is empty
Erase maImages
End Sub

Run Clearup when done, perhaps in from Workbook close event.

Regards,
Peter T

 

[David]

Peter,
Thankyou so much.
You made my day with that solution, works great.
I'll save it in my reference files until I eventually become familiar with
the code methods used.

 

[Peter T]

Hi David,

Glad that was useful.

Was it you who asked a while back about returning the location of an image
control when clicked - if so try this in the click event in the example I
posted:

MsgBox img.TopLeftCell.Address

Regards,
Peter T

 

[David]

Peter,
Yes, it was me, I am now using .topLeftCell to get the contents of cells
local to the each image control (I've got about 40 on each sheet). The local
cell info then becomes criteria for database query(s). The query feeds a
chart and the chart is displayed in a userform; ie the user clicks an image
control and gets the relevant to the location of the image info presented on
a chart. I've still got a bit to do but it's shaping up great thanks to the
help from yourself and others like you.