Copying shapes from one sheet to another for Game

Okay, working on developing a game. This is a simpler template.

Mechanics: Sheet “Random” shuffles the cards. There are 5 shapes (in final version, there will be 27). Each card will have between 1 to 3 of a specific shape on it (Cards will only have 1 shape, not different shapes). Players will double click on a cell to clear the shapes from that cell. Clicking the “Deal” button will replenish the “empty” cells with fresh cards in order of rank from “Random” using VLOOKUP.

Two Issues (Okay, maybe more than two), let’s just tackle the first.
What’s the best way to copy shapes (these are shapes from the “Insert Shapes” menu) from “pics” to the appropriate cells

I have a VLOOKUP finding the shape name and number of times to paste the shape into the cell. . I think my CASE code will position them properly once they are there. I named the cells in which the pics are located as a defined name the same as the pic name (if that helps). For example pics!A1 is pic_1 and pic_1 is located in A1.
Here’s the code I have so far.

Please Login or Register  to view this content.

See Attached. Thanks in advance. I am taking this step by step so I can learn as I go.

Hi,

Please look at my attached file, maybe this is what you expect ?
I have comment my code (all in module2), so there is an explanation of what the next lines will do.
Please tell me what you think.

Regards

Wow, thanks Kare. Seems to work great. I will dissect it and get back to you. Fantastic.

Hi,

Welcome to the wacky world of shapes. You may not need the 'Named Ranges' for your pictures. Although Excel allows duplicate 'Shape Names' on a sheet, it is something I try to avoid. When I create new shapes I either number them sequentially or name them according to some algorithm (e.g. 'myShapeC10' for a shape anchored to cell 'C10').

Attached are a few Shape Utility functions that should help you now and in the future. All were tested successfully with your worksheet:
a. GetActiveShapeAttributes() - Gets the name, coordinates and other attributes associated with a Shape.
b. RenameActiveShape() - Renames a Shape.
c. LoopThroughShapes() - Gets attributes for all Shapes on the Active Sheet. The output is put on Sheet 'Log' (which is created if it doesn't already exist.
d. DeleteAllShapesOnActiveSheet() - Deletes all Shapes on the Active Sheet (including Charts). Comments attached to cells are NOT DELETED.
e. DeleteSpecialShapesOnActiveSheet() - Deletes all Shapes on the Active Sheet that start with a SPECIAL naming convention.
f. CopyShapesToNewSheet() - Example of how to copy Shapes (adapted from your Deal() macro)

Here are a couple of other comments about your code which may help you in the future:
a. I noticed you didn't use 'Option Explicit' at the top of your module. To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

b. When declaring global variables you should probably identify them as being global .e.g.

Please Login or Register  to view this content.

That way you know to look at the top of a module to find it's definition. To the best of my knowledge the KEYWORD 'Global' is archaic and is only present for backward compatibility. You should probably replace it with 'Public'.

Lewis

------------------
Code follows (ordinary Module code such as 'Module1'):

Please Login or Register  to view this content.

Thanks Lewis!
As my primary aim at creating this game is to increase my VBA skills, your specific subroutines, once examined, should help me greatly. I appreciate your comments on Explicit and Public variables too. Hats off to you.