Title a Sub Routine with a variable.

Engineer123

New Member
Joined
Nov 21, 2019
Messages
4
I have over a dozen checkboxes. They are titled checkbox1, checkbox2, checkbox3... etc. They each have a sub routine that tells them what to do when click. These subroutines are identical except for the number that indexes each checkbox. While this was easy to create using copy/paste. It is messy and very difficult to debug, and may get unreasonable should I have to add more checkboxes. My question is...

…is there anyway to have one subroutine that governs all of them? Something like...


Private Sub Checkbox{n}

msgbox( "You have clicked on the " & n & "th checkbox.")

End Sub

Is it possible to have one subroutine that is triggered by any checkbox, and knows which checkbox triggered it?

Thank you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
Are the checkboxes on a sheet, or userform?
If they are on a sheet are they Form Control, or ActiveX?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
In that case you can do something like this.
Create a new Class module & call it ClsChkBx & then put this code in it
VBA Code:
Option Explicit
Public WithEvents ChkBox As MSForms.CheckBox

Private Sub ChkBox_Click()
   MsgBox "you have clicked " & ChkBox.Name
End Sub
Then in the userform module use
Rich (BB code):
Option Explicit
Private ChkBoxClick As Collection

Private Sub UserForm_Initialize()
    Dim Ctrl As MSForms.Control
    Dim Chkbx As ClsChkBx
    
    Set ChkBoxClick = New Collection
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "CheckBox" Then
            Set Chkbx = New ClsChkBx
            Set Chkbx.ChkBox = Ctrl
            ChkBoxClick.Add Chkbx
        End If
    Next
End Sub
The lines in blue must go at the very top of the module, before any code
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top