Lost your password?


Хитрости »
Основные понятия (26)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (20)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (4)

Запись изменений на листе в примечания

Иногда необходимо вести лог изменений в ячейках. Просто чтобы видеть как изменялась информация в ячейке. Например, это может пригодиться при ведении истории заказа, когда статус заказа записывается в одной ячейке. Сначала "В обработке", далее "Вывоз со склада", потом "Доставка" и т.п.
Приведенный ниже код создает примечание в ячейке, если её значение было изменено. В примечание заноситься информация о том, что было занесено в ячейку и когда это было занесено(т.е. дата и время изменения). Если примечание в ячейке уже есть, то в имеющееся примечание допишется информация об изменениях.

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. Чтобы изменить ячейки, в которых необходимо отслеживать изменения, необходимо в строке:
If Intersect(Target, Me.Range("E14:E50")) Is Nothing Then Exit Sub
заменить адрес "E14:E50" на адрес нужных ячеек.

Если необходимо, чтобы в примечание заносилось предыдущее значение ячейки и вдобавок необходимо ограничить диапазон отслеживания изменений конкретными ячейками, то можно применить следующий код:

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. Чтобы изменить ячейки, в которых необходимо отслеживать изменения, необходимо в строке:
If Intersect(Target, Me.Range("A17:I30")) Is Nothing Then Exit Sub
заменить адрес "A17:I30" на адрес нужных ячеек.
Хочу обратить внимание, что при изменении нескольких ячеек сразу код не будет выполняться, т.к. ячеек может быть много и их значения могут просто не уместиться в примечания. Если нужны отслеживания множества ячеек сразу, то имеет смысл ознакомиться со статьей: Ведение журнала сделанных в книге изменений

Скачать пример

  История изменений ячеек в примечаниях (72,5 KiB, 3 691 скачиваний)

Так же см.:
Ведение журнала сделанных в книге изменений
Выделение сделанных изменений


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 37 комментариев
  1. Сергей:

    Подскажите пожалуйста!

    1.Если в строчку скопировать значение, то все комментарии которые там были удаляются.
    2.Больше 3 коментариев не вмещается.
    Можно это все устранить!

  2. 1. Вы явно копируете не только значения. Судя по результату Вы копируете ячейки целиком.
    2. Вмещается больше. Просто надо изменить размер примечания, т.к. не все вмещаются в размер примечения.

  3. Андрей:

    Мне кажется, что лучше бы в примечании отображалось значение ячейки, которое было до внесения изменений. Т.к. новое значение и так видно, достаточно только даты, когда оно было сделано.
    Можно ли дописать код, чтобы это действовало только на определённый диапазон ячеек листа, а не на весь лист. Сейчас получается, что весь лист со временем будет в примечаниях.
    Заранее благодарен,
    Андрей.

  4. Андрей, это конечно так. Но. Внесли одно изменение, затем другое. А старое значение осталось в примечании. В любом случае, расширить можно. Но только сами. В этой статье: Ведение журнала сделанных в книге изменений можете посмотреть реализацию запоминания старого значения и применить к данной статье.

  5. Андрей:

    @Дмитрий(Админ)
    Дмитрий, не могли бы Вы ответить и на вторую часть моего комментария? (про диапазон ячеек)

  6. Конечно.
    Первой строкой кода пишите:

    If Intersect(Target, Me.Range("A1:B10")) Is Nothing Then Exit Sub

    где "A1:B10" это диапазон, изменения в котором отслеживаются.

  7. Андрей:

    @Дмитрий(Админ)
    Спасибо, работает.
    Не хочу злоупотреблять...
    Я не смог разобраться с "предыдущим значением ячейки в ком-те"
    Может за скромную плату допишите?
    (Нужно, чтобы указывалось предыдущее значение(вместо нового) и Автор изменений, если он (Автор) изменился.)
    Заранее благодарен,
    Андрей.

  8. Добавил в статью код записи прежнего значения и всего остального. Пример к статье так же изменен.

  9. Андрей:

    @Дмитрий(Админ)
    В коменте по прежнему отображается новое значение.
    Может я что не так вставил, (скопировал весь код и вставил как есть.)

    "Автор изменений, если он (Автор) изменился" - иначе Автор пишется каждый раз, примечание увеличивается вдвое и не несет информативности. Может это уже слишком...?

  10. Исправил ошибку про запись "прежнего" значения. А вот с автором - да, это уже слишком. Здесь надо морочиться. Андрей - либо изуйчайте VBA, либо переносим общение в личную почту.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2022 Excel для всех   Войти