История изменения ячейки в примечаниях

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

logs-in-comment.png

Чтобы реализовать подобное, нам потребуется несложный макрос. Щелкните правой кнопкой мыши по ярлычку листа, где находятся отслеживаемые ячейки, и выберите команду Исходный текст (View Code). В открывшееся окно скопируйте и вставьте следующий код:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewCellValue$, OldComment$
Dim cell As Range
    
    'если ячейка не в отслеживаемом диапазоне, то выходим
    If Intersect(Target, Range("B3:B5")) Is Nothing Then Exit Sub
    
    'перебираем все ячейки в измененной области
    For Each cell In Intersect(Target, Range("B3:B5"))
        If IsEmpty(cell) Then
            NewCellValue = "Ячейка очищена" 'фиксируем очистку ячейки
        Else
            NewCellValue = cell.Formula     'или ее содержимое
        End If
        On Error Resume Next
        
        With cell
            OldComment = .Comment.Text & Chr(10)
            .Comment.Delete     'удаляем старое примечание (если было)
            .AddComment         'добавляем новое и вводим в него текст
            .Comment.Text Text:=OldComment & Application.UserName & " " & _
                            Format(Now, "MM.DD.YY h:MM:ss") & " : " & NewCellValue
            .Comment.Shape.TextFrame.AutoSize = True    'делаем автоподбор размера
            .Comment.Shape.TextFrame.Characters.Font.Size = 8
        End With
    Next cell
End Sub

Диапазон отслеживания B3:B5 замените на свой - и пользуйтесь на здоровье. 

Ссылки по теме




21.09.2014 23:03:05
при вводе дат в формате "дд.мм.гггг" в ячейки формат в примечаниях числовой получается
10.10.2014 18:03:35
Николай, отличный макрос.

Возможно ли доработать:
1.как скрыть от глаз примечание при вводе в ячейку, т.е. чтобы пользователь-который редактирует данные незнал, что ведется история изменения ячеки?
2. если защитить лист паролем, то Примечание исчезает и не показывается при вводе данных а ячейку,  а при снятии защиты с листа в ячейке примечание очищается!

Спасибо!
03.11.2014 18:27:19
1. Файл - Параметры - Дополнительно отключить отображение индикаторов примечаний (не будет видно даже красных уголков)
2. Когда ставите защиту, то в окне Рецензирование - Защитить лист включите флажок Редактирование объектов. Он позволяет менять примечания при включенной защите листа.
11.11.2014 12:28:23
А можно ли сделать так чтобы, фиксировалось старое содержимое ячейки и через тире на что было изменено?.
14.11.2014 01:18:27
В принципе - да, но нужно ощутимо переписывать весь код макроса.
20.11.2014 15:37:41
Николай, добрый день!
Очень удобная функция.

Но у меня немного другой случай. У меня все ячейки ссылаются на другие книги. Так вот, можно ли такие ячейки отслеживать таким же образом? Даже не обязательно отслеживать кто изменял. Нужно просто, чтобы ячейка сигнализировала, что ее значение поменялось с одного значения на другое, тогда когда в книге источнике изменили значение исходной ячейки.
04.01.2015 09:14:14
Тут нужен совсем другой макрос, этот не подойдет. В вашем случае нужно где-то хранить резервную копию листа и после обновления связей сравнивать ее с рабочим листом, выделяя разницу цветом или помещая ее в примечания.
04.01.2015 08:55:21
Доброго времени суток!
Прежде чем вставить скопированный макрос в свою таблицу по учету товара, Я про экспериментировал чтобы проверить, для страховки. Создал новую книгу  и вставил скопированный макрос в первый лист, но к сожалению он не сработал и стал выдавать ошибку: Compile error. Syntax error OldComment$и меткой в черном фоне части от макроса <span class="mm438w172b3" id="mm438w172b3_3" onclick="window.open('http://click.zeroclickdirect.com/ads-zeroclick/zeroclick/click.do?affiliate=crossrider&subid=500212822721000000&terms=dim','InterYield669188'); return true;" style="height: 14px;">Dim</span> NewCellValue$, при вводе данных в ячейки B3 и B5. Да еще при повторном входе в макрос желтой полоской выделен самое начало макроса:  
Private Sub Worksheet_Change(ByVal Target As Range)    подскажите пожалуйста что нужно сделать для устранения ошибки!?
04.01.2015 09:10:06
Неаккуратно скопировали, видимо. До Private Sub вообще ничего не должно быть.
04.01.2015 09:33:21
С наступившим годом! Благодарствую за оперативный ответ. Попробую по новой.
04.01.2015 09:39:41
Получилось! Еще раз благодарствую! Замечательны сайт, спасибо огромное!
Николай, подскажите как нужно скорректировать код, чтобы примечание записывалось только при определенном значении ячейки (например, если в ячейке будет буква "н"), а при других значениях примечание не записывалось бы? Спасибо!
13.01.2015 09:53:17
Добрый день!

Спасибо за интересный макрос.
Вопрос уже ранее задавался, в ячейках изменяются  даты как сделать, чтобы корректно отображалась измененная дата в примечании?
23.01.2015 15:32:46
Добрый день, есть вопрос, сложно ли поменять макрос на работу при "Защите листа", так как при включении режима защиты, я оставляю несколько столбцов для внесения необходимых данных, и хотел бы видеть кто и что туда вносит, но должен быть уверен что не изменят остальные данные???
Заранее благодарю!
27.01.2015 19:13:57
Добрый день, подскажите как указать тот же диапазон как в примере, но в стиле R1C1
25.02.2015 19:43:54
Чудесно, Николай!

Однако, странная особенность:  при редактировании этого диапазона  с комментами (Range("B3:B5";) . не действует откат/undo по ctrl-z.

При редактировании ячеек вне этого диапазона - работает.
не знаете почему?
10.04.2015 11:12:19
Тут нет нечего странного, после работы макроса нельзя сделать откат
Вне диапазона макрос не срабатывает, поэтому все работает
10.03.2015 05:30:25
Здравствуйте Николай! Очень хорошая книжка, спасибо вам! Проблема - в примечаниях к ячейке, в формате примечания у меня нет вкладки "Цвета и линии", видимо что то в настройках, подскажите где искать?  
21.05.2015 20:30:13
Спасибо, Николай, все отлично работает.
Знаю что можно :) а вот как всю эту инфу копировать на отдельный лист? что-то типа Журнала изменений
02.06.2015 14:50:30
Программка отличная, а как сделать 2 диапазона (2 разные колонки, у меня "прогнозный срок" и "комментарии")
02.06.2015 19:33:05
Понял, нужно так указывать диапазоны, например: Range("J:J, M:M")
09.07.2015 15:33:25
Добрый день!
Спасибо за макрос!
Можно ли как то обойти такой момент, если пользователь вносит изменения способом "скопировал-вставил", копируется и примечание, при вставке стерает всю историю изменений?
Спасибо!
18.08.2015 15:05:09
Николай, прекрасный макрос.
Вопрос:
Как в код форматирующий примечание
.Comment.Text Text:=OldComment & "Last changes:" & " " & Format(Now, "MM/DD/YY  hh:MM") & Format(Chr(10)) & " " & NewCellValue

добавить данную из ячейки А1  ?!
Заранее благодарен.
18.08.2015 15:20:33
Спасибо большое, но нашёл способ сам... :)
Может не совсем правильный, но работает...
 .AddComment   ' Add and enter a new text in it
      .Comment.Text Text:=OldComment & "Last changes:" & " " & Format(Now, "MM/DD/YY  hh:MM") & Format(Chr(10)) _
      & Range("A1") & Range("A3") & Format(Chr(10)) & Range("C1") & Range("C3") & NewCellValue
      .Comment.Shape.TextFrame.AutoSize = True    'auto-fit size
      .Comment.Shape.TextFrame.Characters.Font.Size = 8

Огромное спасибо.
Вот только не понимаю как сделать так, что бы примечание после изменения становилось скрытым. (Hide Comment).
20.04.2016 11:43:14
Здравствуйте. А как сделать что бы отображались корректно данные при формате ячеек ? % и даты
19.07.2016 17:26:58
Здравствуйте, возможно ли использовать данное решение, чтобы откат работал?
26.07.2016 07:25:20
Добрый день!
Скажите, можно ли сделать это немного иначе: изменения записывать не в примечания, а в одну из ячеек справа или слева?
Например, изменения в ячейке A1 записываются в ячейку B1, изменения в ячейке A2 записываются в ячейку B2, изменения в ячейке A3 записываются в ячейку B3 и т.д. Это было бы намного практичнее в большинстве случаев, на мой взгляд.
Нужная вещь, но когда правом на внесение изменений в файл имеет ограниченный круг лиц посредством пароля, макрос не работает. А мне необходимо видеть историю изменений в определенных ячейках, при этом хочется что бы это происходило автоматически (люди могут забыть добавить примечание вручную, или допустить опечатку). Решил проблему добавлением в исходный текст такого макроса:


Sub Refresh_All()
ActiveSheet.Unprotect Password:="123"
ActiveSheet.PivotTables("СводнаяТаблица3").PivotCache.Refresh
ActiveSheet.Protect Password:="123"
End Sub

Здесь 123 – пароль защиты листа (у меня файла), а СводнаяТаблица3 – название сводной таблицы, требующей обновления



Решение увидел здесь
27.03.2017 18:37:24
Странно, сделала все как написано но макрос не работает. Никаких ошибок не выдает. Просто не добавляет в примечание изменения. Даже файл с примером не работает. Настройки проверила, все макросы разрешены...
28.04.2017 11:03:31
Спасибо, очень классный код.

Для тех кто спрашивал по поводу формата изменений ячейки в формате даты, замените в тексте кода:
NewCellValue 
на
Format(NewCellValue, "DD.MM.YY ")
Также если в текст кода вставить снятие и установку защиты листа, то пользователи не смогут удалять эти комментарии.

У меня вопрос, этот макрос входит в конфликт с другим, используемым мной макросом, который запрещает копирование ctrl+c ctrl+v с форматом ячейки и вставляет "как значения". Макрос прописан с модуле "Эта книга" и выдает ошибку на операторе .Undo

Dim IsCopyMode As Boolean
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim a
  If Not IsCopyMode Then Exit Sub
  a = Target.Value
  With Application
    .EnableEvents = False
    .Undo
    Target.Value = a
    .EnableEvents = True
  End With
End Sub
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  IsCopyMode = Application.CutCopyMode = xlCopy
End Sub
Здравствуйте!
Постоянно пользуюсь Вашими советами за что большое спасибо.
Есть защита ячеек макросом в зависимости от цвета ячеек:
http://www.excel-vba.ru/chto-umeet-excel/zashhita-yacheek-v-zavisimosti-ot-cveta/

Но мне нужна защита ячеек без сплывающего окошка.
Мне нужно защитить другие дни которые по условному форматированию будут без заливки ячеек.
сегодняшний день будет с заливкой чтобы его можно было изменять.
Не смог построить этот макрос под себя. людям тоже думаю будет очень нужна такая защита листа
Моя защита строится так:
исключены для ввода только необходимые ячейки обычной защитой листа
проверка данных - на ввод в ячейки только с сегодняшними датами
далее макрос ПРИМЕЧАНИЯ который здесь фиксирующий даже нажатие кнопки del (что очень круто)
далее макрос фиксирующий первый ввод в ячейку, далее макрос фиксирующий последнее изменение ячейки - далее формулой ЕСЛИ ставлю выявление разницы даты ввода и даты последнего ввода (чтобы исключить хитрость изменения даты на компьютере и тем самым исправление других дней).

все супер работает единственная проблема Проверка Данных от эксель не учитывает нажатие кнопки дель то есть могут любой день стереть данные могут даже ненорошно потом я не смогу восстановить. по этому очень нужен макрос который будет защищать в зависимости от цвета заливки, у меня с заливками будут только ячейки сегодняшние.
05.10.2017 09:52:54
Доброго времени суток! Подскажите, пожалуйста, как сделать так, чтобы примечания при изменении содержимого в ячейках появлялись в нескольких диапазонах(столбцах). К примеру интересуют диапазоны "K2:K160", "M2:M160". "N2:N160".
Нужно, чтобы работали они отдельно друг от друга. Пробовал перечислить их через запятую и через точку с запятой- итог один, ошибка. Познаний мало, поэтому не понимаю в чем дело. Помогите!
10.11.2017 14:37:27
Замените везде в коде Range("B3:B5")
на
Range("K2:K160,M2:M160,N2:N160")
Должно работать.
Николай доброе утро!!!
Помогите разобраться... В виду того что в макросе листа уже имеется
Private Sub Worksheet_Change(ByVal Target As Range)

Как можно изменить Ваше наименование кода чтоб применить Ваш макрос. Сразу скажу что в свой код не получится (((( возможно
Или можно ли Ваш код применить "перенести" не в лист а в книгу
Спасибо заранее за ответ
28.02.2018 13:48:10
Здравствуйте! Я уже давно пользуюсь Вашим замечательным макросом, который автоматически добавляет примечания, но кто-то из коллег раскусил как удалять эти примечания! Как их защитить? Спасибо
14.12.2018 11:01:18
Доброго времени суток!
Можно ли сделать отслеживание изменений за определенный период с возможностью выбора периода анализа и поиска определенных значений по выбору для подсчета сколько времени сохранялось выбранное значение в ячейке с суммированием всех таких периодов состояния ячейки с указанным значением. Суть такая: есть перечень оборудования в виде реестра списком перечислено порядка 300 наименований с указанием состояния в данный момент (ТО, Ремонт, Исправно, Монтаж, Демонтаж) нужно отслеживать сколько в таком состоянии находилась ячейка по каждому из видов за заданный период времени (день, неделя, месяц, квартал, год) в выдачей списка периодов с временем изменения (начало-конец) и продолжительностью (дельта), и общей продолжительности за период. Например, как идея, в результате алгоритма на отдельном листе по выбранному для аналите диапазону дублируется списком в 6 столбцов (обозначаю через -- ) Токарный станок - - 1К62 -- Инв. № 00-922304 -- ТО -- (в виде примечания за выбранный период : 12.12.2018 12 :00 : 09 - 13 :45 : 08  = 1,75 часа; 13.12.2018 11 :00 : 09 - 12 :45 : 08  = 1,75 часа; 14.12.2018 14 :00 : 09 - 16 :00 : 08  = 2,00 часа); --Трудоемкость ТО (за выбранный период также как и для примечания) = 5,5 часа. Ну и так далее по выбору состояния в данный момент (ТО, Ремонт, Исправно, Монтаж, Демонтаж), т.е. не все сразу состояния а по выбору. Это своего рода аналитика и отчетность по обслуживаниям и ремонтам оборудования. Готов возместить траты времени  и оплатить Ваши  услуги. berestnevyuriy@mail.ru Юрий.Файл для внедрения аналитики готов предоставить.
Наверх