Solved: Use a variable to reference a control?

[krisk]

I've got an image control and can set it's properties using:

myImage_10.PictureAlignment = 2

...but is there a way to reference a control with a variable or concatenated string? Something analagous to what you can do with shapes, i.e.:

Dim i As Integer
i = 1
With ActiveSheet.Shapes("myOval_0" & i)
.Fill.ForeColor.RGB = RGB(255, 255, 0)
End With

Ideally, something like the following (which of course doesn't work):

ActiveSheet.Controls("myOval_" & i).PictureAlignment = 2

I don't need to loop through all the controls, but just pinpoint one.

[Simon Lloyd]

something similar to
Dim i as Long
Dim MyPic as String
i=1
MyPic= "myImage_"
MyPic & i.PictureAlignment = 2

but if you only want to look at one why all the code?

[Norie]

Where are these controls located?

Userform? Worksheet?

Simon

What you suggest just won't work I'm afraid.:)

[Bob Phillips]

Where are these controls located?

Userform? Worksheet?

Maybe the fact that he included Activesheet in his code was a hint?


Simon

What you suggest just won't work I'm afraid.:)

If you think that, don't you think it would be just a tad more helpful to say why you think that?

[Norie]

xld

I would of thought it was pretty obvious, I mean the code won't even compile.:)

As to the OP's use of ActiveSheet, they also said 'something like'.

But if it is on the worksheet.

Set im = ActiveSheet.Shapes("Image1")
im.OLEFormat.Object.Object.PictureAlignment = 2

[Simon Lloyd]

Simon

What you suggest just won't work I'm afraid.:)Well like you i haven't seen the Op's code or workbook so was trying to give something in the right direction as it looked like he was referencing numbered images, if you look at my suggestion i don't even venture into controls!:sleuth:

[krisk]

something similar to
Dim i as Long
Dim MyPic as String
i=1
MyPic= "myImage_"
MyPic & i.PictureAlignment = 2

but if you only want to look at one why all the code?
Thanks, but it doesn't seem to like that. (Type mismatch on the string, and I don't think there's a way to typecast. Excel 2007, btw.)

This also doesn't work:

Controls("myImage_" & i).PictureAlignment = 2
Basically, I'm trying to create (on a worksheet) an array of image controls (not shapes) accessible by an index, but I gather that VBA doesn't directly support such a thing. Suggested workarounds all use incremental strings ("myImage_" & i) but none of the examples I've found on other sites actually work.

[Norie]

Simon

I wasn't trying to criticise just saying it wouldn't work.:)

You just can't construct variables like that to refer to controls or anything.

Note my comment here.


code won't even compile

[Norie]

Krisk

Have you tried the code I posted?

I know the name is hardcoded but it can easily be adapted.

X = 10
Set im = ActiveSheet.Shapes("Image_" & X)
im.OLEFormat.Object.Object.PictureAlignment = 2

By the way why do you think there's not a way to typecast?:confused:

[Simon Lloyd]

Simon

I wasn't trying to criticise just saying it wouldn't work.:)

You just can't construct variables like that to refer to controls or anything.
I understand that Norie i was only giving a numbering convention for the images as opposed to one for the controls. :)

[krisk]

Krisk

Have you tried the code I posted?

I know the name is hardcoded but it can easily be adapted.

X = 10
Set im = ActiveSheet.Shapes("Image_" & X)
im.OLEFormat.Object.Object.PictureAlignment = 2

Yikes, it works. Thanks. Now I'm off to decode that weird hierarchy (I put the 'new' in 'newbie')


By the way why do you think there's not a way to typecast?:confused: I meant something like I remember using in C:
float myFloat
functionThatWantsAnInteger( int(myFloat) )
MrExcel says this sort of dynamic typecasting isn't directly supported, but I didn't research it further, so take my comment as less than conclusive.

[Norie]

Glad it's working.:)

I know this does seem like a strange hierarchy and I might have just been complicating things.

But via the watch window it appeared to be the only way you could access that particular property.

As to typecasting in VBA there are quite a few type conversion functions eg Val, CInt, CDbl etc

Not quite sure if they would be what you are looking for in this situation since they generally only deal with text and strings.