Using Form Checkboxes

[texasalynn]

I have been trying to find a method using VBA that would allow me to have a generic code to use. I have about 20 sheets and each sheet has from 2 to 20 checkboxes (form not activex). How can I make the code without having to do each and everyone individually?

 

[Norie]

What will the code do?

Will it be the same thing for each checkbox?

If it is then you could assign the same macro to all the checkboxes.

 

[texasalynn]

The code the same thing when checked
When a checked is then unchecked it does something different.

I'm just drawing a blank on how to capture. Here is a small sample, but I don't want to have to do this for every sheet and every checkbox

Option Explicit
Public SRC As String
Private Sub CheckBox1_Click()
    SRC = ActiveSheet.CodeName & "." & Right(CheckBox1.Name, Len(CheckBox1.Name) - 8)
    If Range("E3") = True Then
        Range("I3") = SRC
        Call Chkbx
    End If
    If Range("E3") = False Then
        Range("I3").Clear
        Call RemoveLine
    End If
End Sub

 

[Norie]

If you are using Forms controls then you can use Application.Caller to identify the control that has triggered the macro.

 

[Rick Rothstein]

The code the same thing when checked
When a checked is then unchecked it does something different.

I'm just drawing a blank on how to capture. Here is a small sample, but I don't want to have to do this for every sheet and every checkbox

Option Explicit
Public SRC As String
Private Sub CheckBox1_Click()
    SRC = ActiveSheet.CodeName & "." & Right(CheckBox1.Name, Len(CheckBox1.Name) - 8)
    If Range("[B][COLOR="#FF0000"]E3[/COLOR][/B]") = True Then
        Range("[B][COLOR="#FF0000"]I3[/COLOR][/B]") = SRC
        Call Chkbx
    End If
    If Range("[B][COLOR="#FF0000"]E3[/COLOR][/B]") = False Then
        Range("[B][COLOR="#FF0000"]I3[/COLOR][/B]").Clear
        Call RemoveLine
    End If
End Sub

One example is not usually enough to determine a pattern. How is the checkbox linked to the row and/or column it is affecting? In other words, if we were to come up with some kind of generalized solution (assuming we can do that), how would we be able to tell what cells any particular checkbox affected?

 

[texasalynn]

the things you highlighted are part of what I'm trying to avoid. Say that my checkbox is in row 3, I want to capture that row number and checkbox number. All of the examples I have found does a loop of the checkboxes and I'm not looping. I am wanting the focus to be on the individual checkbox that is chosen. Does that help?

 

[Norie]

Application.Caller will give you the name of the control, in this case checkbox, that has triggered the macro.

From the name you can get a reference to the checkbox itself and can then find the row it's on using it's TopLeftCell property.

Here's a simple example, which goes in a standard module.

To assign it to each checkbox by right click and select Assign macro...

Sub ForCheckBoxes()
Dim chk As Shape
Dim rng As Range

    Set chk = ActiveSheet.Shapes(Application.Caller)
    
    Set rng = chk.TopLeftCell
    
    MsgBox "You clicked " & chk.Name & ", which is in row " & rng.Row & " on sheet " & rng.Parent.Name & "."
    
End Sub

 

[Rick Rothstein]

Say that my checkbox is in row 3, I want to capture that row number and checkbox number.

The checkbox is on the row that it controls... that was part of the link I was looking for. The other part deals with the columns... are they always Columns E and I (as you show in your single example)?

 

[texasalynn]

Norie - I have never been able to make Application Caller work.

Rick - the checkboxes vary, sometimes column E and sometimes Column F.

 

[Norie]

Did you try the code I posted?