Hi VBA Gurus,
I have created checklist with the help of checkboxes in excel sheet (office 365). I want to create log (in same workbook but separate sheet) to keep record of name of person ticked checkbox, time and date.
I have used following code but is does not give date and time (copied code from somewhere)
Dim PreviousValue
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value <> PreviousValue Then
Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
Application.UserName & " changed cell " & Target.Address _
& " from " & PreviousValue & " to " & Target.Value
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PreviousValue = Target.Value
End Sub
Can someone please help how to do that. Excel workbook is attached.
To add date and time, replace:
Application.UserName & " changed cell " & Target.Address _
& " from " & PreviousValue & " to " & Target.Value
with:
Application.UserName & " changed cell " & Target.Address _
& " from " & PreviousValue & " to " & Target.Value & " at: " & Format(Now(),"yyyy-mm-dd-hh-mm-ss")
Hi Catalin,
Thanks for your help . Code has worked for time and date .
Two more things (if you can help please).
1- Log says "changed cell $E$10 from to test 5 on: 01-April-2021 at 20-16. It should say "changed cell $E$10 from empty to test 5 on: 01-April-2021 at 20-16
2- code does not log when tick "checkbox"
Thanks in anticipation.
Regards,
Azid
Hi Azid,
Instead of:
" from " & PreviousValue & " to " & Target.Value
use:
" from " & IIF(PreviousValue<>"",PreviousValue,"empty") & " to " & Target.Value
A checkbox change does not have an event associated, you cannot detect when there is a change. You have to right click the checkbox>Assign Macro>New.
A code is generated for that checkbox, put inside this code:
LogRow Application.Caller
Full procedure will look like:
Sub CheckBox7_Click()
LogRow Application.Caller
End Sub
All checkboxes will have the same code:
Sub CheckBox3_Click()
LogRow Application.Caller
End Sub
Here is the LogRow procedure:
Dim Wks As Worksheet: Set Wks = ThisWorkbook.Worksheets("BAS-QTR")
Dim Cell As String: Cell = Wks.Shapes(Caller).ControlFormat.LinkedCell
Dim CBStatus As String
Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
Application.UserName & " changed " & Caller & Wks.Range(Cell).Row _
& " from " & IIf(Wks.Range(Cell).Value = True, False, True) & " to " & Wks.Range(Cell).Value & " (" & Wks.Cells(Wks.Range(Cell).Row, "B").Value & ")"
End Sub
Hi Catalin,
You are legend.
Thanks. I will try and will let you if there is any issue.
Kind regards,
Azid
Hi Catalin,
VBA showing following error. can you please help.
At the end of my previous message, I provided the LogRow procedure, make sure you add it to your project. Not seeing it in your screen, that's also what the error says, it cannot find it.
Hi Catalin,
Thanks the checkbox log is working now except "checkbox" number system showing is incorrect ( I have attached original sheet and made comments in Log sheet).
Is is possible to add box assign macro so "If my boss clicket rechecked ROW 3 should protected (no one can make any change and color of the box should change to GREEN"
You are helping me a lot. I wish you guy teach JavaScript for excel automation also.
Thanks in anticipation and kind regards,
Azid
These lines needs to be changed:
Application.UserName & " changed " & Caller & " (row " & Wks.Range(Cell).Row & ") " _
& " from " & IIf(Wks.Range(Cell).Value = True, False, True) & " to " & Wks.Range(Cell).Value & " (" & Wks.Cells(Wks.Range(Cell).Row, "B").Value & ")"
I wish you guy teach JavaScript for excel automation also.
It's better to know as much as possible all excel normal tools before going to a complex automation. Many things can be done with normal excel tools, without any automation, excel has a lot of tools and features. Used properly, covers many needs. You need to learn JavaScript language, and that is not excel specific language. Once you know JS, using the specific office JS library will be a walk in the park.
I suggest using the right click event to hide the current row shape and change the cell color, see file attached.
