VBA Excel. Список изменений на листе Excel

Список изменений, внесенных в ячейки указанного диапазона на листе Excel, отслеженных с помощью кода VBA. Запись выявленных изменений на скрытый лист.

Содержание

Для чего нужен список изменений?

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

С помощью кода VBA Excel можно легко организовать отслеживание и запись внесенных изменений в отдельный список на скрытом листе.

Подготовка к отслеживанию изменений

Подготовка рабочей книги к отслеживанию изменений на листе с таблицей или набором данных:

Создайте новый лист Excel с именем ярлыка — «СписокИзменений».
На листе «СписокИзменений» в ячейки A1, B1 и C1 впишите заголовки столбцов таблицы: «Дата и время», «Адрес ячейки» и «Новое значение».
Код процедуры, представленный ниже, разместите в модуле листа, на котором необходимо отслеживать изменения.

Код VBA для отслеживания изменений

Код VBA для отслеживания изменений, внесенных в ячейки заданного диапазона на рабочем листе Excel:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, UsedRange) Is Nothing Then

Application.ScreenUpdating = False

Dim n As Integer

With Worksheets("СписокИзменений")

'Отображаем лист для новой записи

.Visible = True

'Определяем номер первой пустой строки

n = .Range("A1").CurrentRegion.Rows.Count + 1

'Записываем дату и время редактирования

.Cells(n, 1) = Now

'Записываем адрес измененной ячейки

.Cells(n, 2) = Target.Address

'Записываем новое значение измененной ячейки

.Cells(n, 3) = Target.Value

'Скрываем лист со списком изменений

.Visible = False

End With

Application.ScreenUpdating = True

End If

End Sub

Как это работает:

Процедура Worksheet_Change отслеживает ручное изменение значений ячеек в любом месте листа, в модуле которого она размещена.
При обнаружении смены значения ячейки отображается скрытый лист «СписокИзменений», и в новой строке записываются: текущие дата и время, адрес измененной ячейки и ее новое значение.
После записи новой строки лист «СписокИзменений» скрывается.
Чтобы никто не заметил отображение скрытого листа «СписокИзменений», применено отключение обновлений экрана (Application.ScreenUpdating = False).

Список изменений на листе Excel

Примечания

При отслеживании изменения ячеек на всем листе, из процедуры Worksheet_Change можно убрать условие (If Not Intersect).
Если вам нужно отслеживать изменения ячеек в определенном диапазоне, а не на всем листе, замените в условии диапазон UsedRange на отслеживаемый диапазон.

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.