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
--
David

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> David

[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.
--
David


"Peter T" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > --
> > David
>
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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.
> --
> David
>
>
> "Peter T" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > > --
> > > David
> >
> >
> >

[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.

"Peter T" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> > --
> > David
> >
> >
> > "Peter T" wrote:
> >
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > > --
> > > > David
> > >
> > >
> > >
>
>
>