CODEWITHSUNDEEP
excelvbaactivex
Paolo
Paolo

Reputation: 11

ActiveX properties control in VBA

I want to manage ActiveX properties of many ActiveX controls on a worksheet (for instance image1.picture or other similar properties).

Is this possible through Excel VBA code using OLEobjects or Shapes or any other object that accepts to have a variable name in the code, so that this variable name can change with a For loop, replicating in sequence all the names of the ActiveX Controls I want to format?

I am trying to assign properties to Activex Controls in a worksheet, using VBA code. ActiveX Controls are many labels and images in a sheet, and they have sequential names (i.e. Image1, Image2, ..., Label1, Label2, etc.).

I would like to use a For loop (For i = 1 to n) so I can assign specific properties to every Image(i) and Label(i) in the worsksheet, according to the specific index (i).

I can't use OLEobjects or shapes, because they are not supporting the properties I would like to assign. 

With ActiveSheet.oleobjects(variable_name)
    .Width = variable_width               <----- OK, this works
    .Height = variable_height             <----- OK, this works
    .BackStyle = fmBackStyleTransparent   <----- Doesn't work
    .BorderStyle = fmBorderStyleNone      <----- Doesn't work
    .Picture = LoadPicture(path_filename) <----- Doesn't work
End With

.Backstyle, .Borderstyle and .Picture are not supported and the error message is

"run-time error 438: object doesnt support this property"

How can I manage these properties with an object that can have the name expressed with a variable (variable_name), allowing therefore to assign the properties of so many ActiveX Controls in the worksheet just using a For loop, avoiding to type all their names in the VBA code?

Upvotes: 1

Views: 2145

Answers (1)

Tim Williams
Tim Williams

Reputation: 166755

Those properties you're setting do not belong directly to the OLEobject, but to its Object property:

With ActiveSheet.oleobjects(variable_name)
    .Width = variable_width               
    .Height = variable_height             
    .Object.BackStyle = fmBackStyleTransparent   
    .Object.BorderStyle = fmBorderStyleNone     
    .Object.Picture = LoadPicture(path_filename) 
End With

Upvotes: 1

Related Questions