select ActiveX checkbox adjacent cell

[Krystal]

Hi Hope you can help
is there a way to 'select' the adjacent cell for an activex checkbox, without referring to the cell by name.
the following code selects the correct cell, but I have to name change the code for every checkbox and I have dozens of them

This is part of a bigger picture - I have 'Private Sub Worksheet_SelectionChange(ByVal target As Range)'
that works fine but will not execute unless the cell in the target column is selected. so I need to automatically select the cell that the checkbox is in then select the cell in the target column which is offset(0,2)

stating the obvious in the example I have given you the checkbox is named 'TotalPaid3'

If TotalPaid3.Value Then
   TotalPaid3.TopLeftCell.Offset(0, 0).Select
   TotalPaid3.TopLeftCell.Offset(0,2).Select
 Else
   TotalPaid3.TopLeftCell.Offset(0, 0).Select
   TotalPaid3.TopLeftCell.Offset(0, 2).Select


End If
[code/]

 

[davesexcel]

Check this out to get the range of the button clicked.

Excel Questions

 

[Krystal]

Hi Dave thanks for your quick response.
I have checked it out and as far as I can tell 'Application.Caller' only works with form check boxes not activex checkboxes. that is unless I am doing something else wrong

 

[davesexcel]

Possibly something like this,

Private Sub CheckBox1_Click()
    With ActiveSheet.CheckBox1
        If .Value Then
            Range(.TopLeftCell, .TopLeftCell).Offset(0, 2).Select
        Else
            Range(.TopLeftCell, .TopLeftCell).Offset(0, 4).Select
        End If
    End With
End Sub

 

[Krystal]

Sorry for the late reply. please excuse my ignorance, I am new to this. Doesn't your suggestion mean that I would need to replace 'Checkbox1' with the name of my checkboxes. eg 'TotalPaid3', 'TotalPaid4' etc for all checkboxes