setting properties of Activex Controls

[bigt95nt]

I want to use activeX controls, but am haveing problems on how to set the properties programmaticly. When using the macro recorder it records the creation of the but not the setting of the properties.

Anyone Know how to set the properties of ActiveX controls
Tony

 

[shg]

On a userform or a worksheet?

 

[bigt95nt]

On a Worksheet

 

[Rick Rothstein]

You can address the properties of specific ActiveX controls on a worksheet by name via the Object property of an OLEObjects property of the worksheet. For example...

ActiveSheet.OLEObjects("TextBox1").Object.Value = "Hello"
ActiveSheet.OLEObjects("CheckBox1").Object.Value = True
ActiveSheet.OLEObjects("ListBox1").Object.ListIndex = 4

If you need to loop through the controls, Dim an object variable as OLEObject and use it in a For Each loop through the OLEObjects property of your worksheet. For example, let's say you wanted to delete all ActiveX CommandButtons on the worksheet named Sheet1...

Sub DeleteAllCommandTypeButtons()
  Dim Button As OLEObject
  With Worksheets("Sheet1")
     .Buttons.Delete
     For Each Button In .OLEObjects
       If Button.progID Like "*CommandButton*" Then Button.Delete
     Next
  End With
End Sub

 

[Rasm]

Rick - why not just set properties the same way you would if it was on a userform - if you right_click on the active X control in design mode you can set the name of the control as well as any other property from the property window. Or it can be set programatically - in the same way as on a userform. I am asking cuz I have not used it the way you propose. So I just want to understand the difference. I try not to use ActiveX controls directly on the sheet - they seem to feeeze up - so I am wondering if it is the way I set the properties - I mean not setting it as an OLE object.

 

[Rick Rothstein]

Rick - why not just set properties the same way you would if it was on a userform - if you right_click on the active X control in design mode you can set the name of the control as well as any other property from the property window. Or it can be set programatically - in the same way as on a userform. I am asking cuz I have not used it the way you propose. So I just want to understand the difference. I try not to use ActiveX controls directly on the sheet - they seem to feeeze up - so I am wondering if it is the way I set the properties - I mean not setting it as an OLE object.

I think the difference between the two methods is equivalent to the difference referencing a worksheet by either its (tab) Name or its CodeName... one is dynamic and the other is not. The method you use requires you to know the name of the control (which, I'll admit, is probably the majority of the time), but what if you wanted to build the name in code? Let's say you have a series of controls that are related... you could name them all the same and affix a numerical index to the end and use the OLEObjects collection to address them on the fly. For example, let's say you had 10 CheckBoxes that are related, so you name them CBox1, CBox2, etc., then you could to this in code...

' Some code that calculates and assigns the index for the CheckBox, shortened to just the assignment here
Index = 4
' Now use that calculated Index value
ActiveSheet.OLEObject("CBox" & Index).Text = SomeArray(Index)

I'm not sure why I have gotten used to using this method for all ActiveX controls, but I have a vague recollection that there were some properties I could only reach that way. It was so long ago now, however, that I am not completely sure if that was the case or not any more.