Prev: SOS:Number of Working days per week/month
Next: Conditional Format Formula to color row based on text
From: mooresk257 on 9 Apr 2010 13:29
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 9 Apr 2010 13:46
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!
>


 | 
Pages: 1
Prev: SOS:Number of Working days per week/month
Next: Conditional Format Formula to color row based on text