Creating a generic Checkbox_Click VBA Code

Question

I am creating an excel file that is a checklist, currently i have 73 checkboxes in Column D, where in column E it will populate the name of the user based on the username in the options field.

Currently i Have code such as:

Sub CheckBox1_Click()
 If ActiveSheet.CheckBoxes("Check Box 1").Value = 1 Then
   Range("E3").Value = Application.UserName
   Else: Range("E3").Value = ""
 End If
End Sub
Sub CheckBox2_Click() 
If ActiveSheet.CheckBoxes("Check Box 2").Value = 1 Then
   Range("E4").Value = Application.UserName
   Else: Range("E4").Value = ""
 End If
End Sub

For each checkbox in column D. It does work but I need to now replicate Column D into Columns F,H,J,L for other days of the week and I am curious if there is a faster way to do this and a cleaner way to do this instead of having a long list.

score 2 · Accepted Answer · answered Jun 12 '14 at 15:41

2

Try something like this. You will have to format each checkbox and assign this macro to each of them, from the Format | Assign Macro option.

Sub Generic_ChkBox()
Dim cbName As String
Dim cbCell As Range
Dim printValue as String

cbName = Application.Caller

Set cbCell = ActiveSheet.CheckBoxes(cbName).TopLeftCell

Select Case cbCell.Column
    Case 4
        'prints the username in column E
        printValue = Application.UserName
    Case 6
        'prints "Something else" in column G
        printValue = "Something else"
    Case 8
        'prints "etc..." in column I, etc.
        printValue = "etc..."
    Case 10
        printValue = "etc..."
    Case 12
        printValue = "etc..."
End Select

If ActiveSheet.CheckBoxes(cbName).Value = 1 Then
    cbCell.Offset(0, 1).Value = printValue
Else
    cbCell.Offset(0, 1).Value = vbNullString
End If

End Sub

answered Jun 12 '14 at 15:41

David Zemens

53,033
11
81
130

Just for clarity, you can select all the checkboxes and then assign the macro to all of them in one action. – Rory Jun 12 '14 at 15:50
1

`Application.Caller` really is useful for multiple repetitive actions. Has saved me a number of times. – bmgh1985 Jun 12 '14 at 16:00
1

@DavidZemens it wasn't a question, it was an observation. – Rory Jun 12 '14 at 16:02
My mistake -sorry I thought you were asking a follow-up. – David Zemens Jun 12 '14 at 16:06
This works, except it puts the name in E2 instead of E3. I resolved this by changing the offset to (1,1) In case someone was curios – Max Hanft Jun 12 '14 at 18:04
Could be due to the alignment of your checkboxes. I use the `.TopLeftCell` property, so if the checkbox is not aligned within D3, then it will not write to E3. Cheers! – David Zemens Jun 12 '14 at 18:11
Ah that Makes sense, thanks for the help, saved me a lot of time! – Max Hanft Jun 12 '14 at 18:18

score 1 · Answer 2 · answered Jun 12 '14 at 16:57

I have assumed your are going to assign the username value to next cell of CheckBox. For D4 is having checkbox then value will be E4.

Sub ProcessAllCheckBox()
 Dim ws As Worksheet, s As Shape
 Sheets("Sheet1").Columns("A:Z").ClearContents
  Set ws = ActiveSheet
  For Each chk In ActiveSheet.CheckBoxes
   If chk.Value = 1 Then
     Set s = ws.Shapes(chk.Caption)
     Sheets("Sheet1").Range(Cells(s.TopLeftCell.Row, s.TopLeftCell.Column + 1),  Cells(s.TopLeftCell.Row, s.TopLeftCell.Column + 1)).Value = Application.UserName
  End If
 Next

End Sub

Please update the below code in WorkShee Active

Private Sub Worksheet_Activate()
For Each chk In ActiveSheet.CheckBoxes
  chk.OnAction = "ProcessAllCheckBox"
Next
ProcessAllCheckBox
End Sub

Correct, for D3, it will update E3 etc. I am running this one but getting a 400 error THe only change i made was sheet1 to sheet5 in the respective places. — Max Hanft, Jun 12 '14 at 18:02

Creating a generic Checkbox_Click VBA Code

2 Answers2