I have asked this question before, but admittedly in a terrible way, so I am asking this again. So here I am once more.

I am making a program in VBA Excel - or better, combining a certain worksheet with actions and parts of code to make something that works like a stand-alone program. Part of it involves the binding of a number of images (tens of them) to a certain macro, on click.

I have created the said images through the "Developer"->"Design Mode" button. The images are named in the fashion: Image1, Image2, Image3... etc. What I want is, if, for example, I press Image1, the subroutine Action(1), is called. If I press Image100, I want the subroutine Action(100) to be called. Therefore, I want to call the Action Subroutine passing a value according to which image is clicked, in the example I gave you, according to its name.

With a quick search, the first way I found out to make this work was the one below:

Sub Image1_Click()
     Call Action(1)
End Sub

That was for a click on Image1. For a click on Image100, that would be:

Sub Image100_Click()
     Call Action(100)
End Sub  

The downside of using solely this way is obvious: I would need to create as many on-click handlers as the images I have, even though the same routine is called every time and all that matters to get the value to be passed is the image name.

So, given the obvious order of my logic and of what I want to achieve, I would like you to provide me with a more clever way to create on-click events for all my images, without having to use so much redundant code and an obviously not-optimal logic.

NOTE:In case of confusion, the Action Subroutine IS the same for all images. Only the value passed changes.

  • Relevant question referred to as having been asked before. – Raystafarian Apr 28 '14 at 16:54
  • @Raystafarian That's exactly what I was hoping for. I don't know however if that would be possible, I'm not that familiar with VBA. Hence I made this question. Not being realisable is also a possible answer. Note: The Action subroutine IS the same for all images. – Noob Doob Apr 28 '14 at 17:00
  • @Raystafarian What you are saying is like answering that what I ask is not possbile. AVOIDING to write all 100 Image(x)_Click Subroutines is my goal. And you misunderstood. Action(1) does not do the same things as Action(2). I just pointed out that the Action is the same subroutine, and not different for each Image, but the value passed changes what happens. – Noob Doob Apr 28 '14 at 17:04
  • @Raystafarian Action(x) is a subroutine. Simplifying things, you can say that it shows which is the number of the image clicked on. – Noob Doob Apr 28 '14 at 17:08
  • @Raystafarian If Application.Caller gives me a proccessable string, it should be just fine. I shall asnwer the question with it, when I test it. – Noob Doob Apr 28 '14 at 17:15

You can use the Application.Caller property and assign the generic macro to all pictures and then pass the picture's ID as the argument -

Sub genericpicture_click()

Dim pic As String
pic = Application.Caller
MsgBox (pic)

End Sub

Here is an example using cases:

  ' Procedure to display which button was pressed.
      Sub WhichButton()

         ' Assign the calling object to a variable.
         ButtonName = Application.Caller

         ' Display the name of the button that was clicked.
         Select Case ButtonName

            ' NOTE: When you type the name of the button, note that
            ' Visual Basic is case and space sensitive when comparing                                                
            ' strings. For example, "Button 6" and "button6" are not the 
            ' same.
            Case "Button 6"
            MsgBox Application.Caller & "  was Clicked"

            Case "Button 7"
            MsgBox Application.Caller & " was clicked."

            Case "Button 8"
            MsgBox Application.Caller & " was clicked."

         End Select

      End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.