From: mooresk257 on
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!

From: Bob Phillips on
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



--

HTH

Bob

"mooresk257" <mooresk257(a)discussions.microsoft.com> wrote in message
news:586DE7E6-5B84-4BE4-9B78-A3562D49DCEE(a)microsoft.com...
> 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!
>