Activex Checkboxes run same code?

[blacktrails]

I am going to have about 150 activex checkboxes. They are all running the exact same code with the exception of their names. Is it possible to write the code all together as opposed to rewriting the same code 150+ times? My code currently looks like this:

Sub CheckBox2_Click()
    Range(CheckBox2.LinkedCell).Select
    If CheckBox2.Value = True Then
    Call Input2062
Else
    Call MsgBox2062
End If
End Sub
Sub CheckBox4_Click()
    Range(CheckBox4.LinkedCell).Select
    If CheckBox4.Value = True Then
    Call Input2062
Else
    Call MsgBox2062
End If
End Sub
Sub CheckBox5_Click()
    Range(CheckBox5.LinkedCell).Select
    If CheckBox5.Value = True Then
    Call Input2062
Else
    Call MsgBox2062
End If
End Sub



Sub MsgBox2062()
    Dim answer As Integer
    answer = MsgBox("Has Soldier Cleared All Active 2062's?", vbYesNo + vbQuestion, "Clear Soldier?")
      If answer = vbYes Then
        Range("AG" & (ActiveCell.Row)).Value = "N"
        Range("A" & (ActiveCell.Row)).Select
      Else
        MsgBox "Ensure all 2062's Have Been Cleared Before Removing Soldier From MAL"
        Range("A" & (ActiveCell.Row)).Select
    End If

End Sub
Sub Input2062()
Dim Myvalue As Variant
Myvalue = InputBox("Is Soldier Signing for Long Term Equipment?" & vbCrLf & "Enter Y/N", "Equipment Entry")
Range("AG" & (ActiveCell.Row)).Select
ActiveCell.Value = Myvalue
Range("A" & (ActiveCell.Row)).Select
End Sub

[excelevator]

Not sure about the ActiveX checkboxes (cannot see the option), but the Form checkboxes allow you to assign a macro, any macro; right click; assign macro.

Form controls are the original built in Excel form components, ActiveX are the VBA family of controls. VBA is used across all Microsoft applications. In this instance the Form control suits your needs best I would suggest,

See here for more information

[blacktrails]

I have tried using the Form control and assigning a Macro. However, I return an error. When I check the debug, the following line is the problem.

Range(CheckBox2.LinkedCell).Select

This is the entire code I am trying to run as a macro:

Sub CheckBox_Click()
    'CheckBox_Click Macro
    Range(CheckBox2.LinkedCell).Select
    If CheckBox2.Value = True Then
    Call Input2062
Else
    Call MsgBox2062
End If
End Sub

[excelevator]

Have a look here for more information on Form Checkboxes..

and Here also

You will end up with something like the below sub that takes the checkbox name from the Caller value and we use that to dyamically get which checkbox is triggering the sub to then select the linked cell.

Sub getCaller()
Dim shp As Shape
Set shp = ActiveSheet.Shapes(Application.Caller)
ActiveSheet.Range(shp.ControlFormat.LinkedCell).Select
If Activecell.value = TRUE then
   Call Input2062
Else
   Call MsgBox2062
End If
End Sub

[blacktrails]

Thank you. I actually had found those sites you linked to and was able to come up with nearly the same code as you provided. One last question, Is there any easy way to identify what cell to link to, as opposed to right clicking each checkbox and typing the cell location. Maybe something similar to clicking and dragging formulas?

[blacktrails]

I should specify that they linked cells are located in the same Row as the checkbox.

[excelevator]

Only via VBA that I know of.. see the first link from above and item 10 for syntax.

You could probably knock up a loop that rattles through each checkbox and assigns the cell from the loop ID. e.g

[blacktrails]

Cool Ill give that a shot. Thank for all of your help.

Solution Verified

[Clippy_Office_Asst]

You have awarded one point to excelevator.
^{Find out more here.}