Using Object & Worksheet names as variables

M

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!
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top