Lost your password?


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

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

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

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 871 скачиваний)

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


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

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

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

    Давайле в личку.
    Заметил ещё одну проблемку: если до этого в ячейке уже был коммкетарий, то после изменения значения ячейки текст и старый и новый становится жирным.

  2. Андрей:

    Дмитрий, пожалуйста удалите мои лишние посты.
    Модуль полезный, но недоделанный.
    На счет первого варианта: приведите пожалуйста пример, когда в примечании необходимо отображать значение, которое отображено в ячейке.

  3. Андрей :

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

    Если код не выполняет условия именно ВАШЕЙ задачи, это не значит, что он недоделанный. Ищите доделанные тогда.

  4. CTR:

    Дмитрий, огромаднейшее спасибо, нашел прямо то, что искал!

    Единственный вопрос: а можно ли сделать так (и как?), чтобы примечание появлялось не при первом вводе значения, а только при его изменении?

    Начальству, я думаю понравится, с премии поддержу!

  5. В принципе можно. Но код необходимо доработать процедурой из второго кода. Если и так второй код используете, то вариант:

    Private Sub Worksheet_Change(ByVal Target As Range)
    'отслеживаем изменения только в диапазоне "A1:B10"
        If Intersect(Target, Me.Range("A1:B10")) Is Nothing Then Exit Sub
        'проверяем, было ли значение в ячейке до этого
        If sValue = ""  Then Exit Sub
        'сравниваем новое значение с прежним
        If CStr(Target.Value) <> sValue Then
    'далее все так же
  6. CTR:

    Спасибо, Дмитрий!!!
    Работает, ништяк, с меня причитается на пиво!

  7. Андрей:

    Здравствуйте Дмитрий. Возможен ли такой вариант " занесения изменения ячейки в примечание ,только вот изменение в ячейке ставиться формулой , а не в ручную" если да ? Можно узнать как это сделать?

  8. Добрый день, Андрей. Для этого необходимо отслеживать событие листа Calculate. Но там есть сложность отслеживания конкретной ячейки, данные которой изменились, т.к. пересчет формул происходит как правило для всех ячеек, а не для одной конкретной.

  9. Андрей:

    Здравствуйте Дмитрий. Сложность есть, но всё же есть возможность решить её или нет?Например если формула работает только в одном столбце? И изменения нужно отслеживать только для этого столбца?Вот пример макроса для внесения изменений в примечание для одного столбца:

    Private Sub Worksheet_Activate()
    End Sub
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim oComment As Comment
        On Error Resume Next
        Set oComment = Target.Comment
        If Target.Column = 7 Then
                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 If
     
    End Sub

    Может это поможет?

  10. Андрей, выложенный Вами код ничем не поможет. Чтобы было более понятно: у некоторых процедур есть переменные, через которые Excel передает параметр или объект. В случае с Worksheet_Change в процедуру передается объект Target - измененная ячейка или диапазон ячеек. В случае же с Worksheet_Calculate все иначе - в неё не передаются никакие параметры. Следовательно Вы никак не отследите через неё ячейку, в которой была изменена формула. а это означает, что Вам для отслеживания изменений придется: создать Public массив, в который заносить значения всех ячеек с формулами в столбце 7. Затем при каждом пересчете сверять этот массив с новыми значениями и при нахождении расхождений уже менять примечания.

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

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


Для оформления сообщений Вы можете использовать следующие тэги:
<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 Яндекс.Метрика
© 2024 Excel для всех   Войти