Checkbox trigger macro

[rikvny02]

I have a very simple macro to run based off a checkbox and it either doesn't work or it crashed my workbook. Please help.

Private Sub Worksheet_Change(ByVal Target As Range)
If CheckBox12 Then
completed
End If

End Sub


Sub completed()
[j36] = Environ("username") & " " & Now


End Sub

 

[Micron]

I'd say you're crashing because the change event is being called repeatedly by changing j36.
Try

If CheckBox12 Then
    Application.EnableEvents = False
    completed
End If
Application.EnableEvents = True

If you learn how to step through your code (F8) it can save you a lot of trouble. Had you done that here, it probably would have been obvious.

 

[rikvny02]

I'd say you're crashing because the change event is being called repeatedly by changing j36.
Try

If CheckBox12 Then
    Application.EnableEvents = False
    completed
End If
Application.EnableEvents = True

If you learn how to step through your code (F8) it can save you a lot of trouble. Had you done that here, it probably would have been obvious.

I appreciate the response and help. I was not aware i could step the code using F8. You learn something everyday, Thanks

New issue, the solution is not calling the "completed" macro when the box is checked. If I check the box nothing happens however, if i check the box and then run the checkbox code manually it works. Could this be a setting within the checkbox properties?

 

[Micron]

You would need to use an event procedure for the checkbox itself - click or change event.

 

[rikvny02]

Your input is invaluable. Thank you. I believe what your saying is the below code should work? Still no luck.

THE BELOW CODE IS IN A MODULE
Private Sub checkbox1_click()
If CheckBox12 Then
Application.EnableEvents = False
completed
End If
Application.EnableEvents = True
End Sub


Sub completed()
[J36] = Environ("username") & " " & Now
End Sub

 

[Micron]

What module? There are standard modules, sheet modules and userform modules. Code in a standard module must include sheet or userform references, and if necessary, range references as well.

 

[rikvny02]

What module? There are standard modules, sheet modules and userform modules. Code in a standard module must include sheet or userform references, and if necessary, range references as well.

I tried the code in a standard module and also a sheet module. I have never used a user form module. Should the userform module be used in this case.

 

[Micron]

Should the userform module be used in this case.

Not if the checkbox is on a sheet. If that's the case, the code should be in a sheet module.
You know that completed() is not being called because both procedures are in the same module and you stepped through the code as suggested?

 

[rikvny02]

Not if the checkbox is on a sheet. If that's the case, the code should be in a sheet module.
You know that completed() is not being called because both procedures are in the same module and you stepped through the code as suggested?

Everything works great when I moved Completed() to a standard module but I was wondering if you could elaborate a little more on why that is necessary. Your knowledge is invaluable to this novice.

 

[Micron]

To be certain that we're on the same page,
these are sheet modules

these are userform modules

these are standard modules

If the checkbox control is on a sheet and both subs are in a sheet module, both should run as long as the first one runs and checkbox is True.
If checkbox is on a sheet and you moved completed() to a standard module, it should error because completed() would have no awareness of where J36 is. For that reason, I suspect you have your module terminology incorrect. Either that or you did move it to a standard module but included a sheet reference that you're not showing.