История изменения ячейки в примечаниях
В некоторых случаях весьма полезным может быть сохранение всей истории изменения какой-либо ячейки в ее же примечании. Выглядеть это может примерно так:
Чтобы реализовать подобное, нам потребуется несложный макрос. Щелкните правой кнопкой мыши по ярлычку листа, где находятся отслеживаемые ячейки, и выберите команду Исходный текст (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 замените на свой - и пользуйтесь на здоровье.
Ссылки по теме
- Как узнать кто из пользователей входил в вашу книгу Excel в последнее время
- Основные способы установки защиты (ячеек, листов, книги) в Excel
Возможно ли доработать:
1.как скрыть от глаз примечание при вводе в ячейку, т.е. чтобы пользователь-который редактирует данные незнал, что ведется история изменения ячеки?
2. если защитить лист паролем, то Примечание исчезает и не показывается при вводе данных а ячейку, а при снятии защиты с листа в ячейке примечание очищается!
Спасибо!
2. Когда ставите защиту, то в окне Рецензирование - Защитить лист включите флажок Редактирование объектов. Он позволяет менять примечания при включенной защите листа.
Очень удобная функция.
Но у меня немного другой случай. У меня все ячейки ссылаются на другие книги. Так вот, можно ли такие ячейки отслеживать таким же образом? Даже не обязательно отслеживать кто изменял. Нужно просто, чтобы ячейка сигнализировала, что ее значение поменялось с одного значения на другое, тогда когда в книге источнике изменили значение исходной ячейки.
Прежде чем вставить скопированный макрос в свою таблицу по учету товара, Я про экспериментировал чтобы проверить, для страховки. Создал новую книгу и вставил скопированный макрос в первый лист, но к сожалению он не сработал и стал выдавать ошибку: 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) подскажите пожалуйста что нужно сделать для устранения ошибки!?
Спасибо за интересный макрос.
Вопрос уже ранее задавался, в ячейках изменяются даты как сделать, чтобы корректно отображалась измененная дата в примечании?
Заранее благодарю!
Однако, странная особенность: при редактировании этого диапазона с комментами (Range("B3:B5";) . не действует откат/undo по ctrl-z.
При редактировании ячеек вне этого диапазона - работает.
не знаете почему?
Вне диапазона макрос не срабатывает, поэтому все работает
Знаю что можно
Спасибо за макрос!
Можно ли как то обойти такой момент, если пользователь вносит изменения способом "скопировал-вставил", копируется и примечание, при вставке стерает всю историю изменений?
Спасибо!
Вопрос:
Как в код форматирующий примечание
добавить данную из ячейки А1 ?!
Заранее благодарен.
Может не совсем правильный, но работает...
.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).
Скажите, можно ли сделать это немного иначе: изменения записывать не в примечания, а в одну из ячеек справа или слева?
Например, изменения в ячейке A1 записываются в ячейку B1, изменения в ячейке A2 записываются в ячейку B2, изменения в ячейке A3 записываются в ячейку B3 и т.д. Это было бы намного практичнее в большинстве случаев, на мой взгляд.
Private Sub Worksheet_Change(ByVal Target As Range) Dim NewCellValue$, OldComment$ Dim ce1l As Range Dim cCd As Integer Dim cRd As Integer 'если ячейка не в отслеживаемом диапазоне, то выходим If Intersect(Target, Range("i3:i1000000";)) Is Nothing Then Exit Sub 'перебираем все ячейки в измененной области For Each cell In Intersect(Target, Range("i3:i1000000";)) 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 cCd = cell.Column + 1 'адрес ячейки на 1 правее от изменяемой cRd = cell.Row 'адрес ячейки на одной строке с изменяемой Cells(cRd, cCd) = Format(Now, "MM.DD.YYYY";) Next cell End SubSub Refresh_All()
ActiveSheet.Unprotect Password:="123"
ActiveSheet.PivotTables("СводнаяТаблица3").PivotCache.Refresh
ActiveSheet.Protect Password:="123"
End Sub
Здесь 123 – пароль защиты листа (у меня файла), а СводнаяТаблица3 – название сводной таблицы, требующей обновления
Решение увидел
Для тех кто спрашивал по поводу формата изменений ячейки в формате даты, замените в тексте кода:
У меня вопрос, этот макрос входит в конфликт с другим, используемым мной макросом, который запрещает копирование ctrl+c ctrl+v с форматом ячейки и вставляет "как значения". Макрос прописан с модуле "Эта книга" и выдает ошибку на операторе .Undo
Постоянно пользуюсь Вашими советами за что большое спасибо.
Есть защита ячеек макросом в зависимости от цвета ячеек:
Но мне нужна защита ячеек без сплывающего окошка.
Мне нужно защитить другие дни которые по условному форматированию будут без заливки ячеек.
сегодняшний день будет с заливкой чтобы его можно было изменять.
Не смог построить этот макрос под себя. людям тоже думаю будет очень нужна такая защита листа
исключены для ввода только необходимые ячейки обычной защитой листа
проверка данных - на ввод в ячейки только с сегодняшними датами
далее макрос ПРИМЕЧАНИЯ который здесь фиксирующий даже нажатие кнопки del (что очень круто)
далее макрос фиксирующий первый ввод в ячейку, далее макрос фиксирующий последнее изменение ячейки - далее формулой ЕСЛИ ставлю выявление разницы даты ввода и даты последнего ввода (чтобы исключить хитрость изменения даты на компьютере и тем самым исправление других дней).
все супер работает единственная проблема Проверка Данных от эксель не учитывает нажатие кнопки дель то есть могут любой день стереть данные могут даже ненорошно потом я не смогу восстановить. по этому очень нужен макрос который будет защищать в зависимости от цвета заливки, у меня с заливками будут только ячейки сегодняшние.
Нужно, чтобы работали они отдельно друг от друга. Пробовал перечислить их через запятую и через точку с запятой- итог один, ошибка. Познаний мало, поэтому не понимаю в чем дело. Помогите!
на
Range("K2:K160,M2:M160,N2:N160")
Должно работать.
Помогите разобраться... В виду того что в макросе листа уже имеется
Как можно изменить Ваше наименование кода чтоб применить Ваш макрос. Сразу скажу что в свой код не получится (((( возможно
Или можно ли Ваш код применить "перенести" не в лист а в книгу
Спасибо заранее за ответ
Можно ли сделать отслеживание изменений за определенный период с возможностью выбора периода анализа и поиска определенных значений по выбору для подсчета сколько времени сохранялось выбранное значение в ячейке с суммированием всех таких периодов состояния ячейки с указанным значением. Суть такая: есть перечень оборудования в виде реестра списком перечислено порядка 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 Юрий.Файл для внедрения аналитики готов предоставить.
Николай спасибо большое за макрос, очень помог.
Всем нам удачи.
Замечательный макрос, очень пригодился.
Подскажите пожалуйста как приделать выключатель, т.е. при определенных условиях не вносить изменения в комментарии.
Например: если А1=0, макрос не вносит изменения, во всех остальных случаях работает как должен.
Возник вопрос, а можно каким-то образом поднять историю изменения в ячейках. Скажем "злоумышленник" внес изменения в ячейку. Позже это было замечено. Вопрос кто "злоумышленник" и когда он это сделал?
Как доработать макрос чтобы первое внесение в данную ячейку - комментарий не осуществлялся - только при повторной корректировке ячеек из диапазона?!
я пытася прописать вместо диапазона следующее значение (Target, Range("Tracker_list[[#Headers],[ACTION]] ")- тут текст в смайл превращается.
формула стала ругаться, или так же на умные таблицы нужно макросы иначе писать ?
Как сохранить возможность отката ?
Спасибо за макрос! Подскажите, пожалуйста, можно ли сделать так, что бы в комментариях так же учитывалось изменение цвета ячейки?
Подскажите пож-та, а как историю комментария записывать на другой лист, с сохранением координат cell?
и куда нужно код поставить в модуль или эта книга?
Как сделать чтобы этот код в ячейках с числами указывал в формате чисел, а дату - в формате даты? Т.е. нужно в одном коде разместить 2 диапазона с разными форматами.