show hide shape to a cell according to its value

Some help please?

Hi pavlos_x,

There is a discussion of how Shapes work in this post, and a solution to your problem in the next post.

Thank you for uploading the example file. It would have been impossible for me to try to help you without the file.

In order to understand the solution, you have to understand how Shapes work on a Worksheet:

a. In order to access a Shape using VBA (unless the Shape is SELECTED), the Shape Name must be UNIQUE on that Sheet.

b. When creating Shapes using Excel Drawing Tools, or Forms Controls Tools, or Active X Tools, Excel creates unique Shape Names.

c. When copying shapes created in b. above, Excel will increment the number associated with the Shape Name. For example when cutting and pasting Forms Control 'Button 1', the Shape display in the NEW SHAPE will remain 'Button 1', but the NAME of the Shape will be 'Button2'.

d. Once the User Renames a Shape then Excel DOES NOT increment the the number associated with the Shape Name. For example Forms Control 'Button 1' is created, and then renamed 'Button 11'.

When cutting and pasting 'Button 11', the Shape display in the NEW SHAPE will remain 'Button 11', and the NAME will REMAIN 'Button11'. If VBA is not involved this in not a problem for Drawing Objects or Forms Control Shapes.

NOTE: This is NOT TRUE for 'Active X' Controls. 'Active X' controls maintain their UNIQUE names when cutting and pasting. If 'Active X' control names are NOT UNIQUE, unpredictable results can and will occur.

e. When accessing a Shape by name using VBA, the Shape name MUST be UNIQUE on the Sheet. Excel maintains a list of Shape Names (probably in the order they were created). When requesting a Shape name, Excel matches the first matching name that is found. If there were three Shapes 'ABC' on the Sheet, Excel would reference the same 'ABC' each time 'ABC' was requested. The other 2 'ABC' shapes would never be accessed.

Lewis

In order to create UNIQUE Shape names, some kind of system is required. The two most common systems I use are:
a. Sequential numbering system (e.g. 'RCD01', 'RCD02', etc.)
b. Cell Reference system (e.g. 'RCDH9', RCDH10', etc.)

In your case, there is a MASTER Shape that you created, called 'RCD'. Your application lends itself the the 'Cell Reference System'. I deleted all your 'RCD' (Clone) Shapes, except for the MASTER shape. In cells 'H3' thru 'H62' I created clone 'RCD' shapes with names 'RCDH03' thru 'RCDH62'. To do this I used the following macros:
a. DeleteAllRCDCloneCellsOnActiveSheet() - needed only during Software Development.
b. CreateShapesRCD() -needed only during Software Development.
c. DisplayOrHideShapesRCD() - need during Software Development and in Production if the Shape Visibility gets out of sync due to some kind of corruption.

In an ordinary code module such as 'Module1':

Please Login or Register  to view this content.

When you change the values in Column H, you need the following code in Worksheet_Change() in the Sheet module. I took the liberty of updating your existing code:

Please Login or Register  to view this content.

All the above code is included in the attached file based on your uploaded file.

Lewis

wow this is amazing!! thank you so much. you did so much work on it. the way that these things work is unbelievable, for a small process two pages of coding!!!