Запись изменений на листе в примечания
Иногда необходимо вести лог изменений в ячейках. Просто чтобы видеть как изменялась информация в ячейке. Например, это может пригодиться при ведении истории заказа, когда статус заказа записывается в одной ячейке. Сначала "В обработке", далее "Вывоз со склада", потом "Доставка" и т.п.
Приведенный ниже код создает примечание в ячейке, если её значение было изменено. В примечание заноситься информация о том, что было занесено в ячейку и когда это было занесено(т.е. дата и время изменения). Если примечание в ячейке уже есть, то в имеющееся примечание допишется информация об изменениях.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'отслеживаем изменения только в диапазоне "E14:E50" - т.е. только статус '(изменить адрес, если надо отслеживать другие ячейки) If Intersect(Target, Me.Range("E14:E50")) Is Nothing Then Exit Sub Dim oComment As Comment On Error Resume Next Set oComment = Target.Comment If oComment Is Nothing Then Target.AddComment Target.Text & " " & Format(Now, "dd.mm.yy HH:MM") Else oComment.Text oComment.Text & Chr(10) & Target.Text & " " & Format(Now, "dd.mm.yy HH:MM") End If End Sub |
Код необходимо поместить в модуль листа(щелкнуть правой кнопкой мыши по ярлычку листа -Исходный текст), изменения на котором необходимо отследить. Подробнее о модулях.
Следует учитывать, что код сработает только если данные были изменены вручную и для одной ячейки, а не для нескольких. Если скопировать в вставить несколько ячеек, примечание будет создано только для одной, а текст примечания может отличаться от ожидаемого. Если изменения производятся посредством вычисления формул - код не сработает вообще.
Изменения отслеживаются исключительно для ячеек A17:I30. Чтобы изменить ячейки, в которых необходимо отслеживать изменения, необходимо в строке:
заменить адрес
Если необходимо, чтобы в примечание заносилось предыдущее значение ячейки и вдобавок необходимо ограничить диапазон отслеживания изменений конкретными ячейками, то можно применить следующий код:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim v, vv, sf, sa As String 'отслеживаем изменения только в диапазоне "A17:I30"(изменить адрес, если надо отслеживать другие ячейки) If Intersect(Target, Me.Range("A17:I30")) Is Nothing Then Exit Sub 'если изменено более одной ячейки - завершаем выполнение во избежание ошибок If Target.Count > 1 Then Exit Sub 'получаем новое значение v = Target.Value sf = Target.Formula 'запоминаем адрес текущей выделенной ячейки 'т.к. после Undo она изменится и надо будет вернуть sa = Selection.Address 'получаем старое значение With Application .EnableEvents = 0 .Undo vv = Target.Value 'старое значение 'возвращаем последнее записанное значение Target.Formula = sf Me.Range(sa).Select .EnableEvents = 1 End With 'сравниваем новое значение с прежним If CStr(vv) <> CStr(v) Then 'если значения различаются - создаем или дописываем примечание On Error Resume Next Dim oComment As Comment Set oComment = Target.Comment If oComment Is Nothing Then 'примечания еще нет - создаем и записываем информацию об изменениях Set oComment = Target.AddComment(CreateObject("wscript.network").UserName & ":" & Chr(10) & "было: " & vv & "; стало: " & v & "; Дата: " & Format(Now, "dd.mm.yy HH:MM")) Else 'уже есть примечание - дописываем информацию об изменениях oComment.Text oComment.Text & Chr(10) & CreateObject("wscript.network").UserName & ":" & Chr(10) & "было: " & vv & "; стало: " & v & "; Дата: " & Format(Now, "dd.mm.yy HH:MM") End If oComment.Shape.TextFrame.AutoSize = True End If End Sub |
Код так же как и предыдущий размещается в модуле листа(правая кнопка мыши по ярлычку листа -Исходный текст), изменения в котором необходимо отслеживать.
Так же в данном коде помимо старого значения в примечание так же записываемся имя пользователя, изменившего значение, новое значение, дата/время изменения. Изменения отслеживаются исключительно для ячеек A17:I30. Чтобы изменить ячейки, в которых необходимо отслеживать изменения, необходимо в строке:
заменить адрес
Хочу обратить внимание, что при изменении нескольких ячеек сразу код не будет выполняться, т.к. ячеек может быть много и их значения могут просто не уместиться в примечания. Если нужны отслеживания множества ячеек сразу, то имеет смысл ознакомиться со статьей: Ведение журнала сделанных в книге изменений
История изменений ячеек в примечаниях (72,5 KiB, 3 886 скачиваний)
Так же см.:
Ведение журнала сделанных в книге изменений
Выделение сделанных изменений
Статья помогла? Поделись ссылкой с друзьями!
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Спасибо за Дмитрий. За информацию. Будем стараться.
Дмитрий, огромное спасибо за интересный макрос.У меня вопрос.После внесения изменений в ячейках добавляется комменты от дате и кем внесены изменения.Но добавляются эти комменты сверху в низ. Можно сделать так чтобы вверхний коммент был сверху
Сейчас
Ruslan:
07.02.14 10:35
Ruslan:
9999 07.02.14 10:47
Хотелось бы :):
Ruslan:
9999 07.02.14 10:47
Ruslan:
07.02.14 10:35
Ruslan, замените строку:
На такую:
Очень оперативно:) Спасибо, я там запрос Вам отправил на работу,будет время прошу ответить:)
Здравствуйте, подскажите пожалуйста, я вставляю код в модуль листа и закрываю окно, но ничего не происходит, я не могу понять где смотреть изменения?
Александр,
1. Кроме закрытия окна неоходимо еще изменить значение какой-либо ячейки.
2. Макросы должны быть разрешены.
3. Написано четко и ясно - изменения записываются в примечание к ячейке.
Классный макрос сотворили Вы Дмитрий!
Добрый день. Можно еще в этом коде прописать так, чтобы сохранялось только два, три..значения. А не целая история. И еще маленький момент, примечание не растягивается, при нескольких значениях. Их не видно. Можно здесь кое-что сделать?
Авторазмер добавил. Для Только трех значений - надо переделывать код и еще подумать, как определять кол-во сделанных изменений, т.к. на основании одного только текста примечания это будет не всегда верно.
Здравствуйте, подскажите пожалуйста, как можно сделать чтобы последний комментарий выводился сверху для вкладки "Прежнее значение"
Доброго всем дня и выходных! Нужна помощь. Есть простой макрос, который по кнопке вводит время в выделенную ячейку (в моем случае в столбце "М") и одновременно вводит дату изменения ячейки столбца "М" в соседний, или указанный столбец в этой же строке (в моем случае в столбец "L"). Задача для меня не подъемная: нужно,что бы дата в столбце "L" менялась один раз за день не зависимо от количества изменений в течении дня в ячейке столбца "М". Однократное введение даты необходимо потому, что изменения записываются в комментарий, и если дата будет в течении дня меняться неоднократно,то в комментарии получится много записей с одной и той же датой...
Sub Занято()
If Not Intersect(ActiveCell, Range("J14:M350")) Is Nothing Then ActiveCell = Format(Now, "hh:mm") & "-занято"
ActiveCell.EntireRow.Cells(12) = Date ' дата в столбец 12 той же строки
End Sub