Запись изменений на листе в примечания
Иногда необходимо вести лог изменений в ячейках. Просто чтобы видеть как изменялась информация в ячейке. Например, это может пригодиться при ведении истории заказа, когда статус заказа записывается в одной ячейке. Сначала "В обработке", далее "Вывоз со склада", потом "Доставка" и т.п.
Приведенный ниже код создает примечание в ячейке, если её значение было изменено. В примечание заноситься информация о том, что было занесено в ячейку и когда это было занесено(т.е. дата и время изменения). Если примечание в ячейке уже есть, то в имеющееся примечание допишется информация об изменениях.
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 873 скачиваний)
Так же см.:
Ведение журнала сделанных в книге изменений
Выделение сделанных изменений
Статья помогла? Поделись ссылкой с друзьями!
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Подскажите пожалуйста!
1.Если в строчку скопировать значение, то все комментарии которые там были удаляются.
2.Больше 3 коментариев не вмещается.
Можно это все устранить!
1. Вы явно копируете не только значения. Судя по результату Вы копируете ячейки целиком.
2. Вмещается больше. Просто надо изменить размер примечания, т.к. не все вмещаются в размер примечения.
Мне кажется, что лучше бы в примечании отображалось значение ячейки, которое было до внесения изменений. Т.к. новое значение и так видно, достаточно только даты, когда оно было сделано.
Можно ли дописать код, чтобы это действовало только на определённый диапазон ячеек листа, а не на весь лист. Сейчас получается, что весь лист со временем будет в примечаниях.
Заранее благодарен,
Андрей.
Андрей, это конечно так. Но. Внесли одно изменение, затем другое. А старое значение осталось в примечании. В любом случае, расширить можно. Но только сами. В этой статье:Ведение журнала сделанных в книге изменений можете посмотреть реализацию запоминания старого значения и применить к данной статье.
Дмитрий, не могли бы Вы ответить и на вторую часть моего комментария? (про диапазон ячеек)
Конечно.
Первой строкой кода пишите:
где "A1:B10" это диапазон, изменения в котором отслеживаются.
Спасибо, работает.
Не хочу злоупотреблять...
Я не смог разобраться с "предыдущим значением ячейки в ком-те"
Может за скромную плату допишите?
(Нужно, чтобы указывалось предыдущее значение(вместо нового) и Автор изменений, если он (Автор) изменился.)
Заранее благодарен,
Андрей.
Добавил в статью код записи прежнего значения и всего остального. Пример к статье так же изменен.
В коменте по прежнему отображается новое значение.
Может я что не так вставил, (скопировал весь код и вставил как есть.)
"Автор изменений, если он (Автор) изменился" - иначе Автор пишется каждый раз, примечание увеличивается вдвое и не несет информативности. Может это уже слишком...?
Исправил ошибку про запись "прежнего" значения. А вот с автором - да, это уже слишком. Здесь надо морочиться. Андрей - либо изуйчайте VBA, либо переносим общение в личную почту.