Иногда необходимо вести лог изменений в ячейках. Просто чтобы видеть как изменялась информация в ячейке. Например, это может пригодиться при ведении истории заказа, когда статус заказа записывается в одной ячейке. Сначала "В обработке", далее "Вывоз со склада", потом "Доставка" и т.п.
Приведенный ниже код создает примечание в ячейке, если её значение было изменено. В примечание заноситься информация о том, что было занесено в ячейку и когда это было занесено(т.е. дата и время изменения). Если примечание в ячейке уже есть, то в имеющееся примечание допишется информация об изменениях.
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 КиБ, 3 970 скачиваний)
Так же см.:
Ведение журнала сделанных в книге изменений
Выделение сделанных изменений
Добрый день!
Скажите, можно ли сделать это немного иначе: изменения записывать не в примечания, а в одну из ячеек справа или слева?
Например, изменения в ячейке A1 записываются в ячейку B1, изменения в ячейке A2 записываются в ячейку B2, изменения в ячейке A3 записываются в ячейку B3 и т.д. Это было бы намного практичнее в большинстве случаев, на мой взгляд.
Сделать можно. Вместо этого кода:
запишите такой:
P.S. И не надо дублировать свой вопрос в комментариях к нескольким статьям.
Похоже, что переменные предложенного кода несколько отличаются от исходного. И в исходном коде отсутствует эта часть, которую предложено заменить. Час пытался разобраться, но моих знаний не хватает, а функция очень нужна. Могли бы Вы, пожалуйста, проверить Ваше сообщение?
Рустам, код уже переписывался с момента публикации, статье уже не один год, а комментарии достаточно старые. Хотя даже в этом виде та часть, которую надо заменить, прослеживается логически. По сути все, что надо - заменить объект примечания ссылкой на нужную ячейку. Сейчас этот блок выглядит так:
и замены должны быть соответствующие:
Спасибо большое! Разобрался. Можете, пожалуйста еще подсказать, почему после того, как макрос выполняет команду, стандартное действие "отмена действия" [Ctrl + Z] - не работает?
Могу ответить - потому что это известное ограничение Excel при применении макросов. Предвидя следующий вопрос:Как отменить действия макроса
Здравствуйте. А если этот код интегрировать в общую гугл таблицу, можно ли добавить в примечание еще емаил того кто вносит изменения?
Или допустим я знаю все емаилы , присвоить каждому конкретное имя и вставлять автоматически его ? Спасибо !
Михаил, если Вы можете интегрировать это в Google - то полагаю, что получить какие-то email тоже будет не самой большой проблемой. Но лично я в этом не помогу, т.к. не уверен, что в Google можно вообще отслеживать изменения и создавать примечания скриптами.
Здравствуйте, Дмитрий!
Очень помог Ваш скрипт! Спасибо!
Но вот только в примечании, перед текстом добавляются 6 пробелов в строке, в первых двух ячейках и три пробела в следующих двух и опят 6 пробелов в завершающей, что смещает текст в примечании и неудобно читаемо, так как не работает автоформатирование.
R2:V34"
Подскажите, пожалуйста, что можно сделать?
Галина, ищите проблему в Ваших данных. Код сам никаких пробелов не добавляет.
Добрый день , скажите пожалуйста как защитить примечания ,которые сделал макрос, от редактирования и удаления
Ольга, думаю не получится. Для этого надо будет защищать лист, что наложит ограничения в том числе и на действия макросом. Можно, конечно, каждый раз при изменении снимать защиту с листа тем же макросом, потом устанавливать. Но это может сделать процесс изменения данных на листе достаточно неприятным. Получится что-то вроде этого:
Других вариантов нет. Чтобы побольше узнать про защиту и как разрешить при этой защите изменять ячейки:Защита листов и ячеек в MS Excel