Use a Variable to Reference a Check Box Object

[Ken Hudson]

In a larger section of code I am trying to use a variable to reference a
check box. I have listed code samples below to attempt to explain my
question. Assume that I have six columns of data. In row one I have dates. In
row two I have check boxes. I want to loop through the columns, comparing two
sets of dates at a time. Based on that comparison, I want to hit a check box
in one of the two columns below those dates. The first code section does what
I want but is not efficient.
Is there a way to code the change in the second section to somehow use the
lColCount variable to identify the check box to be hit?


Option Explicit

Dim lColCount As Long
Sub PopCheckBoxes()
For lColCount = 1 To 6 Step 2
If Cells(1, lColCount) > Cells(1, lColCount + 1) Then
If lColCount = 1 Then
ActiveSheet.CheckBoxes("Check box 1").Value = xlOn
Else
ActiveSheet.CheckBoxes("Check box 2").Value = xlOn
End If
If lColCount = 3 Then
ActiveSheet.CheckBoxes("Check box 3").Value = xlOn
Else
ActiveSheet.CheckBoxes("Check box 4").Value = xlOn
End If
If lColCount = 5 Then
ActiveSheet.CheckBoxes("Check box 5").Value = xlOn
Else
ActiveSheet.CheckBoxes("Check box 6").Value = xlOn
End If
End If
Next lColCount
End Sub
----------------------------------------------------------------------
Sub PopCheckBoxes()
For lColCount = 1 To 6 Step 2
If Cells(1, lColCount) > Cells(1, lColCount + 1) Then
'(Can I use lColCount to reference this check box) = xlOn
Else
'(Can I use lColCount to reference this check box) = xlOn
End If
Next lColCount
End Sub

 

[Jacob Skaria]

Try the below

ActiveSheet.CheckBoxes("Check box " & lColCount).Value = xlOn

If this post helps click Yes

 

[Jacob Skaria]

Adjust the value of lColCount to suit your requirment..

If this post helps click Yes

 

[Dave Peterson]

Option Explicit
Sub PopCheckBoxes()
Dim lColCount As Long 'moved inside the procedure
With activesheet
For lColCount = 1 To 6 Step 2
If .Cells(1, lColCount).value > .Cells(1, lColCount + 1).value Then
.CheckBoxes("Check box " & lcolcount).Value = xlOn
.CheckBoxes("Check box " & lcolcount + 1).Value = xloff
Else
.CheckBoxes("Check box " & lcolcount).Value = xlOff
.CheckBoxes("Check box " & lcolcount + 1).Value = xlOn
End If
Next lColCount
End with
End Sub

Don't you want the "opposite" checkbox unchecked?

 

[Ken Hudson]

Jacob and Dave,
Thanks for the reponses. These references don't seem to work.
As I think about this, I don't think this can be done anyway.
These check boxes are floating objects that can be placed anywhere on the
worksheet and can't be assigned to a specific cell.
I think they can only be addressed by their object name, e.g. "Check box 6."
Does this seem correct to you?

 

[Dave Peterson]

That code didn't do anything based on the location of the checkboxes. It did
everything based on the name of the checkbox.

But you didn't explain how the suggested code didn't work for you.

 

[Ken Hudson]

Hi Dave,
I am just now realizing what is going on.
I need to name the check boxes to include the varible the matches the column
number in which the check box is sitting. That is how the code will work
with the column variable.
The check boxes already have names asigned that I need to change. Is there a
trick to renaming them? When I right click on the object and go to the Name
Box in Excel to rename it, it doesn't seem to work. When I rename "Check Box
48" to "Check Box 3" and hit the return key, the curser jumps to another cell
and the name doesn't change.

 

[Dave Peterson]

I'd remove the checkboxes and re-add them.

But you should be able to rename them using the technique you described. I'd
guess that you actually named a range using that string.

Insert|name|define
and you can see if you have any of those "oopsies" names to delete.

If the checkboxes could be placed anywhere, you may find naming the checkboxes
based on the cells that "control" them would be better.

If you can specify the range(s) that get the checkboxes and what cells control
each of them, the macro to add these checkboxes isn't too difficult to
implement.

Hi Dave,
I am just now realizing what is going on.
I need to name the check boxes to include the varible the matches the column
number in which the check box is sitting. That is how the code will work
with the column variable.
The check boxes already have names asigned that I need to change. Is there a
trick to renaming them? When I right click on the object and go to the Name
Box in Excel to rename it, it doesn't seem to work. When I rename "Check Box
48" to "Check Box 3" and hit the return key, the curser jumps to another cell
and the name doesn't change.