How to track form control changes

[Dharris144]

Hello VB experts I could really use some help on recording changes to a a form control such as a check box.
I have the check boxes associated to cell ( one for each control different control)
I have this code to record changes to a "log" sheet. It is working fine but when I check a check box or tick an option control it does not log as a change.
Thanks in advance for any help you can provide.






Private Sub Workbook_Open()
Sheets("log").Visible = xlSheetVeryHidden
End Sub


Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
If Sheets("log").Visible = xlSheetVisible Then
Sheets("log").Visible = xlSheetVeryHidden
Else
Sheets("log").Visible = xlSheetVisible
End If
Target.Offset(1, 1).Select






End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name <> "log" Then
Application.EnableEvents = False
Sheets("log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & "-" & Target.Address(0, 0)
Sheets("log").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Target.Value
Sheets("log").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Environ("username")
Sheets("log").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Now
Sheets("log").Columns("A:D").AutoFit
Application.EnableEvents = True
End If




End Sub

 

[AlphaFrog]

Assign this macro to each of your form controls you want logged. It will trigger a worksheet change event based on the control's linked cell.

[color=darkblue]Sub[/color] Form_Control_Change()
    [color=darkblue]With[/color] Range(ActiveSheet.Shapes(Application.Caller).DrawingObject.LinkedCell)
        .Value = .Value
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Dharris144]

Thanks for the quick reply but I am not sure what you mean by assign to each control
Do I have to reference each cell address in the code?

 

[AlphaFrog]

• Paste the code in a standard code module e.g. Module1
• Right-click on each Form control and select Assign Macro from the pop-up context menu
• Select the Form_Control_Change macro and Okay

 

[Dharris144]

Good lord I missed the obvious LOL

Sometimes Yes it is just that easy.

Thanks AF