Using Object & Worksheet names as variables

[mooresk257]

Let's see if I can ask this is a way that makes sense:

I have a worksheet with nine image boxes on it named Image1-9. I have
identical code for each image box that handles photo insertion and deletion.
I think I can clean up the code by having the image_click() procedure call a
sub function, rather than duplicate the image handling code nine times for
each imagebox click event.

Question #1:

I can collect the worksheet name as:

Dim SheetID as String

SheetID = ActiveSheet.Name

How do I collect the name (Image1) from the the object that triggers the
click event?

Question #2:

How do I pass these variables to my photo handling code, and construct the
code to reference these variables?

Here's my current photo handling code which I want to make into a sub
function to call with the image click event:

Private Sub Image1_Click()

Dim NewImg As Long
Dim DelImg As Long

NewImg = MsgBox("Insert New Photo?", vbYesNoCancel)
If NewImg = vbYes Then
FileToOpen = Application.GetOpenFilename( _
"All Files (*.jpg),*.jpg, All Files (*.bmp),*.bmp")
If FileToOpen <> False Then
Worksheets("Sheet1").OLEObjects("Image1").Object.Picture _
= LoadPicture(FileToOpen)
With Image1
.BackColor = &H80000005
.BorderStyle = fmBorderStyleNone
End With
End If
ElseIf NewImg = vbNo Then
If Worksheets("Sheet1").OLEObjects("Image1"). _
Object.Picture Is Nothing Then
GoTo Skip
End If
DelImg = MsgBox("Remove Current Photo?", vbYesNo)
If DelImg = vbYes Then
Worksheets("Sheet1").OLEObjects("Image1").Object.Picture =
LoadPicture("")
With Image1
.BackColor = &H8000000F
.BorderStyle = fmBorderStyleSingle
End With
ElseIf DelImg = vbNo Then
End If
ElseIf NewImg = vbCancel Then
End If
Skip:

End Sub

Thanks!

 

[Bob Phillips]

You don't need the worksheet, as the image will be associated with that.

In the caller, you just have to use the same name as the event.

SO, in a standard module add

Public Sub HandleIMage(mImage As Object)
Dim NewImg As Long
Dim DelImg As Long
Dim FileToOpen As Variant

NewImg = MsgBox("Insert New Photo?", vbYesNoCancel)
If NewImg = vbYes Then
FileToOpen = Application.GetOpenFilename( _
"All Files (*.jpg),*.jpg, All Files (*.bmp),*.bmp")
If FileToOpen <> False Then
mImage.Object.Picture = LoadPicture(FileToOpen)
With mImage
.BackColor = &H80000005
.BorderStyle = fmBorderStyleNone
End With
End If
ElseIf NewImg = vbNo Then
If Not mImage.Object.Picture Is Nothing Then

DelImg = MsgBox("Remove Current Photo?", vbYesNo)
If DelImg = vbYes Then
mImage.Object.Picture = LoadPicture("")
With mImage
.BackColor = &H8000000F
.BorderStyle = fmBorderStyleSingle
End With
ElseIf DelImg = vbNo Then
End If
End If
End If
End Sub

Then the image click events will be like so

Private Sub Image1_Click()
Call HandleIMage(Image1)
End Sub