1 reply to this topic

[Broo2u]

Hello Everybody, I just registered here in hopes of getting this question answered. This is apparently a very challenging question! So far, I have not been able to find information through Google, or on one other forum that touts itself as the "best" help for VBA for Excel. I also looked in Microsoft's knowledge base, and found nothing there either. But, maybe I haven't used the right wording in my searches.?

I have created an array of Image Controls on an Excel worksheet. They are laid out in ten rows of four. They each have a name (i.e. "Grid1A"), but they also have shape index numbers from 7 through 46.

I am able to change a picture on one of the Image Controls with the following code (using shape with name "Grid1A" and shape index number 7 as an example):

Sheets("Sheet1").Shapes.Grid1A.Picture = LoadPicture("H:\My Pictures\picture.jpg")

For some reason, when I went to make the pictures visible / not visible, the system preferred that I use this format:

ActiveSheet.Shapes("Grid1A").Visible = True OR
ActiveSheet.Shapes(7).Visible = True

Now, I want to be able to change the picture on different elements of my array of image controls, based on what the user enters somewhere else on the form. Because of the complexity of the code, it would save a lot of space (from reusing code) if I could somehow use a variable to reference the shapes.

The following both generate runtime error messages that "the object does not support the property or method, but they are both along the lines of what I would like to be able to do:

Dim strMyGridName As String
strMyGridName = "Grid1A"
ActiveSheet.Shapes(strMyGridName).Visible = True


Dim intMyGridNumber As Integer
intMyGridNumber = 7
ActiveSheet.Shapes(intMyGridNumber).Visible = True

So, does anybody know how you can pass a variable in place of a shape name or shape index number, and then load a picture with that?

Thanks so much if you can help!

[marwex89]

Dim shapeID As String
shapeID = "Grid1A"
Shapes.Item(shapeID).Visible = False

This worked fine for me... Try it. :)
But what version of Excel are you using?