Wednesday 18 April 2018

Assign triggered code to multiple images - Excel VBA



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.



Answer



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.


No comments:

Post a Comment

Where does Skype save my contact's avatars in Linux?

I'm using Skype on Linux. Where can I find images cached by skype of my contact's avatars? Answer I wanted to get those Skype avat...