Relative Referencing Control Names when using ActiveX Checkbox Control

[WilliamAnton]

I chose to use ActiveX Checkbox Controls. While I like the formatting options I'm not liking my inability to reference the control name in the code so that I don't have to duplicate the same code from row to row (with each row having a corresponding calculation impact on a associated data field. After hours and hours of researching and reading I'm not finding a way to do the equivalent of the activesheets logic available in the Form Control objects. Is there a coding solution for this? Thank you in advance. I'm losing my mind and as I'm not the most technical person here, there's not much left to lose.

 

[Worf]

I understand the controls are on the worksheet, not inside a user form. Is this correct?

' sheet module
Private Sub Worksheet_Activate()
Dim i%, olb As OLEObject
For i = 1 To Me.OLEObjects.Count
    Set olb = Me.OLEObjects(i)                                              ' by index
    If InStr(olb.Name, "Check") > 0 Then MsgBox "Name: " & olb.Name & _
    vbLf & "Location: " & olb.TopLeftCell.Address
Next
MsgBox Me.OLEObjects("CheckBox1").Object.Value, 64, "Check Box Status"      ' by name
End Sub

 

[WilliamAnton]

Thank you for the quick response. Yes, the objects are on a worksheet. I'll be getting back to my PC in a couple of hours. Dinner time here.

Please forgive my basic understandings. I'm trying to move from "form controls" to ActiveX and I'm lost. I get the layering of the object model conceptually, but really struggling with coding.

I'll add your code when I get home. Is there a street level explanation of what outcome I will be getting? Is this code listing the addresses of the objects? I was hoping to write a function that I could call that put the Active Control name automagically into generic code that would also pull the relative data & columns I'm acting on.

Thank you again for your kindness.

 

[Worf]

When the sheet is activated, the code will give you the names and worksheet position of all controls that have the word “check” in their names. This part is done referencing the controls by sequential indexing (1, 2, 3...)

Cell position is useful when you want to manipulate data on the same row (or any other relative row or column, actually)
After that, the code informs if one check box is selected, this time referencing it by name.

To ease the programming task, I would suggest you name them CheckBox1, CheckBox2… although this is not mandatory.
If you provide more details on what you want to accomplish, I can write more example code.

 

[WilliamAnton]

Hello Worf. Thanking you again for your help. Getting the following error. "Compile error. Invalid use of Me keyword." I copied your code into a Button as follows:

Sub Worksheet_Activate_Click()


Dim i%, olb As OLEObject
For i = 1 To Me.OLEObjects.Count
Set olb = Me.OLEObjects(i) ' by index
If InStr(olb.Name, "Check") > 0 Then MsgBox "Name: " & olb.Name & _
vbLf & "Location: " & olb.TopLeftCell.Address
Next
MsgBox Me.OLEObjects("CheckBox1").Object.Value, 64, "Check Box Status" ' by name


End Sub

<hr/>

I don't know how much more effort to put into these ActiveX Checkbox Controls. I converted the Checkboxes in my spreadsheet to Forms Control Checkboxes and got the functionality I needed. I'm such a rookie at this it took a bit, but it's working. I still have an interest in learning ActiveX coding in the long run. I'd like to understand these types of errors, but under time pressures to finish this project up right now. I'm off to coding similar functionality for Forms Control Comboboxes. wish me luck.

 

[Worf]

The code below worked for me. If you need help with form controls, post here.

' sheet module
Private Sub CommandButton1_Click()                                      ' ActiveX button
Dim i%, olb As OLEObject
For i = 1 To Me.OLEObjects.Count
    Set olb = Me.OLEObjects(i)                                          ' by index
    If InStr(olb.Name, "Check") > 0 Then MsgBox "Name: " & olb.Name & _
    vbLf & "Location: " & olb.TopLeftCell.Address
Next
MsgBox Me.OLEObjects("CheckBox1").Object.Value, 64, "Check Box Status"  ' by name
End Sub