1

I wanted to assign some specific actions when clicking on one of the images I have inserted in my Excel sheet. The problem is, I got 52 images (Poker cards for those wondering). So, my question is, is there any way to avoid writing code for 52 cases, and find a way like below:

Private Sub Image(x)_Click()
   Call common_subroutine(x)
End Sub

, instead of writing the same thing 52 times, one for each different image name? Since I use the same subroutine to do the work with a variable according to the case, the load of work won't be that big if there's no way to do it, but it surely bothers me whether this obviously silly way is the only one possible.

0

3 Answers 3

0

I've encountered this problem several times while programming user forms. Unfortunately, I never found a way to get around creating a trigger for each object individually. However, I did think to use Python to print out the actual code that I could then paste into the userform VBA. No need for the tedious number changing this way. (I suppose you could do this in VBA as well, but good luck doing it in one line.)

The Python loop below would work for the example you posted.

for i in range(52):
    print "Private Sub Image%d_Click()\n\tCall common_subroutine(%d)\nEnd Sub\n" % (i + 1, i + 1)

Just run from the command prompt and copy the output.

3
  • True, that's the way to go when you got no choice left. But as a programmer, it really is a pain in the eye to have such a thing when your logic is so simple :P
    – Noob Doob
    Apr 28, 2014 at 17:24
  • 1
    Not sure if application.caller works well for userforms, but I did manage to get it to work for the pictures. relevant Q&A, but it might need some trimming to get just the number. Apr 28, 2014 at 17:34
  • @Raystafarian Funny, I was just checking out Application.Caller after thinking about this a little bit more. Your solution would work outside of a userform with Application.Caller built into the macro.
    – Excellll
    Apr 28, 2014 at 17:41
1

Okay, I'm sort of confused at your VBA example, but the best way to do this is to multi-select all the pictures, right click - assign macro.

This will assign the same macro to all of the selected pictures.

1
  • The exact same macro? I don't want the exact same action to be performed, I want to use the same subroutine BUT with a single different parameter, according to the image clicked. For example, if Image2 is clicked, the click event will call the Sub ChangeStats(2).
    – Noob Doob
    Apr 15, 2014 at 12:14
0

Yes. Give each image the same name, and a different index. You can now refer to this object as a whole collection and select an image by whatever index is given.

4
  • How shall that be done?
    – Noob Doob
    Apr 9, 2014 at 13:47
  • I assume you know how you can change the properties of the images you placed, given that you were able to place them in the first place? You click the image, and then in the property sheet, you change the variables Name and index. Note that if you have an image with a name, and you set its index property to 0, if you then copy/paste that image, it will have the same name with an index of +1.
    – LPChip
    Apr 9, 2014 at 13:56
  • I don't see the index property, and whenever I try to use the same name for 2 images, I get a warning: "Ambiguous name detected" and the previous name is restored.
    – Noob Doob
    Apr 9, 2014 at 13:59
  • Ah. I'm sure this is the way to go as I've done it countless times. I currently do not have access to excel so I can't dive into it. The only thing I can think of is, that you used the wrong toolbar to insert components such as images. There should be two, where one should have the image with index property.
    – LPChip
    Apr 9, 2014 at 14:03

Not the answer you're looking for? Browse other questions tagged .