Lost your password?


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

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

Проблема очень актуальна для больших организаций, когда одним файлом пользуются несколько человек. И каждый может сделать какие-то свои изменения. И конечно всегда наступает момент когда надо узнать - а кто сделал то или иное изменение? Возможно просто для информации, а бывает и хуже, когда необходимо узнать кто внес конкретное изменение, которое делать было нельзя и по возможности восстановить хоть часть того, что было.


Отслеживание изменений при помощи встроенных средств - Общий доступ к книге
Есть относительно простой способ отслеживать изменения(если это можно так назвать): дать книге общий доступ
Excel 2007 и выше: вкладка Рецензирование(Review) -Доступ к книге(Share workbook).
В более новых версиях(таких как Excel 2021 и выше, а так же Office 365) на вкладке Рецензирование(Review) нет команды Доступ к книге(Share workbook). Она спрятана, т.к. считается устаревшей и вместо неё Microsoft принуждает использовать общий доступ через OneDrive. Достать устаревшую возможность общего доступа можно через меню:
Файл(File) -Параметры(Options) -либо Панель быстрого доступа(Quick Access Toolbar) либо Настроить ленту(Customize ribbon) -в списке Выбрать команды из(Choose commands from) выбираем Все команды(All commands) и находим там пункт: Общий доступ к книге(старые версии)(Share workbook(Legacy) и Выделить исправления(Highlight changes). Перемещаем их либо на панель быстрого доступа(если она была выбрана изначально), либо на ту же вкладку Рецензирование(Review), если команда выбиралась в Настроить ленту(Customize ribbon).
После этого выбираем команду Общий доступ к книге(старые версии)(Share workbook(Legacy)

В появившемся окне поставить галочку разрешить изменять файл нескольким пользователям одновременно(Allow changes by more then one user at the same time):
Доступ к файлу
Далее можно настроить срок хранения лога изменений, конфликты и пр - вкладка Подробнее(Advanced):
Параметры доступа
Регистрация изменений(Track changes)

  • Хранить журнал в течение(keep change history for): - если необходимо вести журнал изменений(а нам необходимо!) то оставляем этот пункт включенным и устанавливаем количество дней, в течение которых необходимо сохранять историю. По умолчанию это 30 дней. Здесь имеются ввиду последние 30 дней от текущей даты. Т.е. по истечению этих 30 дней более ранние данные истории будут затерты
  • Не хранить журнал изменений(don't keep change history): после выбора этого пункта и подтверждения журнал будет удален(если он был создан) и история вестись не будет

Обновлять изменения

  • При сохранении файла(When file is saved) - это самый оптимальный вариант. Данные об изменениях в файле будут обновляться только тогда, когда мы сами сохраним файл.
  • Каждые(Automatically every): указывается промежуток времени в минутах, через который книга сама автоматически будет сохраняться и регистрировать изменения. Не очень удобен данный пункт если в файле одновременно работает несколько человек. При этом необходимо будет обязательно выбрать какое действие будет производится по умолчанию:
    • сохранить мои изменения и просмотреть чужие(save my changes and see others' changes)
    • только просмотреть чужие изменения(just see other users' changes)

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

Для противоречивых изменений(Conflicting changes between users)

    данный пункт управляет разрешением конфликтов, если при одновременном пользовании файлом несколькими пользователями два и более пользователя сделали изменения в одной и той же ячейке листа.

  • запрашивать чьи изменения имеют преимущество(ask me which changes win) - самый оптимальный вариант. Первый, открывший файл пользователь определяет какие изменения надо принять, а какие отклонить
  • ранее сохраненные имеют преимущество(the changes being saving win) - не очень правильный вариант, но все зависит от ситуации. По логике при данном пункте при возникновении конфликта автоматически будут приняты лишь те изменения, которые были сделаны ранее. Может сыграть нехорошую шутку, поэтому надо быть острожным с этим пунктом

Включить в личное представление(Include in personal view)

    Данный пункт управляет настройками печати и фильтра общей книги для нескольких пользователей.

  • параметры печати(Print settings) - Обычно, в одном файле когда мы настраиваем параметры печати, они сохраняются внутри файла и при следующем открытии их не надо уже заново настраивать. Здесь тоже самое, но хранится для каждого пользователя отдельно. Т.е. даже если в этом файле один пользователь настроил одни параметры печати, а другой – иные, то для каждого пользователя эти параметры сохраняться. В обычной книге применились бы те параметры, которые были назначены перед последним сохранением книги.
  • фильтры(Filter settings) - если один пользователь отфильтровал данные по "Юго-Восточный округ", а другой тот же столбец по "Северный округ", то при установленном данном пункте у каждого пользователя файл откроется с отфильтрованными строками именно по установленным ими параметрам - для каждого свой
  • Но оба эти пункта имеют большой недостаток: в зависимости от количества пользователей и их действий они могут сильно "раздувать" файл и приводить к значительным его "тормозам". Поэтому без необходимости лучше их не использовать

Теперь самое главное: как увидеть все сделанные изменения
После того, как пользователи поработали с файлом и стало необходимо увидеть сделанные изменения необходимо перейти на вкладке Рецензирование(Review) -Исправления(Track changes) -Выделить исправления(Highlight changes)
Просмотреть изменения
Здесь можно выбрать какие изменения показывать

  • по времени(When) - если хотите увидеть только какие-то конкретные изменения, то надо установить галочку на этом пункте и выбрать нужное. Доступно выбрать: Со времени последнего сохранения, Все, Еще не просмотрено, С даты. Пункты достаточно красноречивы и понятны, расписывать каждый не вижу смысла. Если хотите просмотреть все изменения - галочку с этого пункта надо снять
  • пользователем(Who) можно показать изменения, сделанные конкретным пользователем, всеми пользователями, или всеми пользователями, кроме того, кто запросил отчет об изменениях(т.е. кроме себя любимого)
  • в диапазоне(Where) можно указать конкретный диапазон на листе и отчет об изменения будет выведен только для ячеек этого диапазона.

Выделять исправления на экране(Highlight changes on screen): если установить эту галочку, то изменения будут созданы в виде примечаний к ячейкам, изменения в которых были сделаны. В левом верхнем углу ячейки в этом случае появится черный треугольник, а при наведении на эту ячейку появится примечание с информацией о том кто изменил, когда и на что:
Изменения в примечании

Вносить изменения на отдельный лист(List changes on a new sheet): в этом случае будет создан новый лист с именем "Журнал", в котором будут перечислены ячейки, в которые были внесены изменения с указанием даты и времени изменения, пользователя сделавшего изменение, старое и новое значение измененной ячейки:
Изменения в отдельном листе

Примечание: После того, как книге дан общий доступ книгу одновременно могут менять несколько пользователей. Однако я не рекомендую делать это без крайней необходимости, т.к. одновременный доступ к файлам Excel реализован очень плохо и работает это нестабильно. В какой-то момент файл может просто отказаться работать и все данные будут утеряны. Так же неизбежно будут возникать спорные ситуации, когда изменения внесли два человека одновременно и не сохранилось в результате ни одно из внесенных.

Но самый главный недостаток: книги в общем доступе имеют ряд ограничений, среди которых такие как:

  • невозможно удалять листы
  • невозможно создавать диаграммы, можно лишь просматривать созданные ранее
  • невозможно создавать сводные таблицы, можно лишь просматривать созданные ранее
  • невозможно создавать или изменять группировку данных, можно использовать ранее созданную
  • невозможно изменять параметры защиты листов и книги
  • невозможно использовать расширенный фильтр
  • невозможно использовать Текст по столбцам
  • невозможно создавать новые проверки данных, а так же изменять существующие. Допускается лишь обвести или удалить обводку с неверных данных
  • невозможно добавлять или изменять ранее созданные формулы массива
  • и т.д.

Плюс невозможно не только использовать умные таблицы, но и сделать книгу общей, если в ней есть хоть одна умная таблица. Если будет попытка сделать общий доступ к книге с умной таблицей Excel покажет предупреждение, что этого делать нельзя и проинструктирует как преобразовать такую таблицу в диапазон для возможности использовать общий доступ.
Так же хочу отметить, что есть распространенное заблуждение о невозможности использования макросов в книгах с общим доступом. Это не так, коды Visual Basic for Applications разрешается применять и в большинстве случаев они будут работать корректно и как задумывались, если они только не пытаются произвести действия, перечисленные как запрещенные для книг с общим доступом. Плюс невозможно просматривать и изменять коды в книгах с общим доступом.



 
Отслеживание изменений и ведение журнала при помощи кода
Изменения можно отслеживать и при помощи кода. При этом такой метод дает не менее полное представление об изменениях в ячейках и при этом давать общий доступ книге нет необходимости, а следовательно и все ограничения, применимые для книг в общем доступе тоже остаются за бортом, что делает такой подход порой предпочтительнее. Единственное, при таком режиме файл нельзя будет редактировать одновременно нескольким пользователям. Но в большинстве случаев этого и не надо.
Я могу предложить небольшой код, который будет отслеживать следующие параметры:

  • Имя пользователя(учетная запись пользователя на компьютере), сделавшего изменения
  • адрес ячейки, в которую были внесены изменения
  • дата и время внесения изменений
  • имя листа, в котором были сделаны изменения
  • значение ячейки до изменения(старое значение)
  • значение ячейки после изменения(новое значение).

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

Option Explicit
Public sValue As String
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "LOG" Then Exit Sub
    Dim sLastValue As String
    Dim lLastRow As Long
 
    With Sheets("LOG")
        lLastRow = .Cells.SpecialCells(xlLastCell).Row + 1
        If lLastRow = Rows.Count Then Exit Sub
        Application.ScreenUpdating = False: Application.EnableEvents = False
        .Cells(lLastRow, 1) = CreateObject("wscript.network").UserName
        .Cells(lLastRow, 2) = Target.Address(0, 0)
        .Cells(lLastRow, 3) = Format(Now, "dd.mm.yyyy HH:MM:SS")
        .Cells(lLastRow, 4) = Sh.Name
        .Cells(lLastRow, 5).NumberFormat = "@"
        .Cells(lLastRow, 5) = sValue
        If Target.Count > 1 Then
            Dim rCell As Range, rRng As Range
            On Error Resume Next
            Set rRng = Intersect(Target, Sh.UsedRange): On Error GoTo 0
            If Not rRng Is Nothing Then
                For Each rCell In rRng
                    If Not IsError(Target) Then sLastValue = sLastValue & "," & rCell Else sLastValue = sLastValue & "," & "Err"
                Next rCell
                sLastValue = Mid(sLastValue, 2)
            Else
                sLastValue = ""
            End If
        Else
            If Not IsError(Target) Then sLastValue = Target.Value Else sLastValue = "Err"
        End If
        .Cells(lLastRow, 6).NumberFormat = "@"
        .Cells(lLastRow, 6) = sLastValue
    End With
    Application.ScreenUpdating = True: Application.EnableEvents = True
End Sub
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "LOG" Then Exit Sub
    If Target.Count > 1 Then
        Dim rCell As Range, rRng As Range
        On Error Resume Next
        Set rRng = Intersect(Target, Sh.UsedRange): On Error GoTo 0
        If rRng Is Nothing Then Exit Sub
        For Each rCell In rRng
            If Not IsError(rCell) Then sValue = sValue & "," & rCell Else sValue = sValue & "," & "Err"
        Next rCell
        sValue = Mid(sValue, 2)
    Else
        If Not IsError(Target) Then sValue = Target.Value Else sValue = "Err"
    End If
End Sub

Что такое модуль книги и как туда вставить код подробно описано в этой статье. Если кратко: открываем редактор VBA(Alt+F11) -находим в списке объектов ЭтаКнига(ThisWorkbook) -двойной щелчок по ней и в окно редактора справа вставляется этот код.

Лист "LOG" рекомендую сделать скрытым, иначе смысла в отслеживании действий мало, т.к. любой сможет перейти на этот лист и стереть историю своих изменений. Надежно скрыть лист поможет эта статья: Как сделать лист очень скрытым.


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

Option Explicit
Public sValue As String
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "LOG" Then Exit Sub
    Dim sLastValue As String
    Dim lLastRow As Long, wbLOG As Workbook
    Dim sPath as String
    Const sLOGName As String = "\LOG.txt" '"\LOG.xls"
    sPath = Application.DefaultFilePath
    Application.ScreenUpdating = False
    '==============   только для записи в текстовый файл   ======================
    If Dir(sPath & sLOGName, vbDirectory) = "" Then
        Open sPath & sLOGName For Output As #1: Close #1
    End If
    '==============   только для записи в отдельный файл Excel ======================
'    If Dir(sPath & sLOGName, vbDirectory) = "" Then
'        Set wbLOG = Workbooks.Add
'        wbLOG.SaveAs sPath & sLOGName, xlNormal
'    End If
    Set wbLOG = Workbooks.Open(sPath & sLOGName)
    '============================================================================
    With wbLOG.Sheets(1)
        lLastRow = .Cells.SpecialCells(xlLastCell).Row + 1
        If lLastRow = .Rows.Count Then Exit Sub
        Application.ScreenUpdating = False: Application.EnableEvents = False
        .Cells(lLastRow, 1) = CreateObject("wscript.network").UserName
        .Cells(lLastRow, 2) = Target.Address(0, 0)
        .Cells(lLastRow, 3) = Format(Now, "dd.mm.yyyy HH:MM:SS")
        .Cells(lLastRow, 4) = Sh.Name
        .Cells(lLastRow, 5).NumberFormat = "@"
        .Cells(lLastRow, 5) = sValue
        If Target.Count > 1 Then
            Dim rCell As Range, rRng As Range
            On Error Resume Next
            Set rRng = Intersect(Target, Sh.UsedRange): On Error GoTo 0
            If Not rRng Is Nothing Then
                For Each rCell In rRng
                    If Not IsError(Target) Then sLastValue = sLastValue & "," & rCell Else sLastValue = sLastValue & "," & "Err"
                Next rCell
                sLastValue = Mid(sLastValue, 2)
            Else
                sLastValue = ""
            End If
        Else
            If Not IsError(Target) Then sLastValue = Target.Value Else sLastValue = "Err"
        End If
        .Cells(lLastRow, 6).NumberFormat = "@"
        .Cells(lLastRow, 6) = sLastValue
    End With
    wbLOG.Close 1
    Application.ScreenUpdating = True: Application.EnableEvents = True
End Sub
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "LOG" Then Exit Sub
    If Target.Count > 1 Then
        Dim rCell As Range, rRng As Range
        On Error Resume Next
        Set rRng = Intersect(Target, Sh.UsedRange): On Error GoTo 0
        If rRng Is Nothing Then Exit Sub
        For Each rCell In rRng
            If Not IsError(rCell) Then sValue = sValue & "," & rCell Else sValue = sValue & "," & "Err"
        Next rCell
        sValue = Mid(sValue, 2)
    Else
        If Not IsError(Target) Then sValue = Target.Value Else sValue = "Err"
    End If
End Sub

Файл хранится в папке "Мои документы" пользователя. Имя файла - LOG.txt задается посредством константыConst sLOGName As String = "\LOG.txt"

Чтобы вести изменения в отдельной книге Excel надо будет всего лишь закомментировать строки под "только для записи в текстовый файл" и раскомментировать строки под "только для записи в отдельный файл Excel" и поменять значение для константыConst sLOGName As String = "\LOG.xls"
Не следует оставлять оба этих блока - они противоречат друг другу и если оставить оба, то будет создан текстовый файл, но изменения все равно будут заноситься в отдельную книгу Excel.
Если хотите, чтобы файл с историей изменений хранился в папке, отличной от Мои документы, то необходимо
Application.DefaultFilePath заменить на нужный путь, к примеру такой:sPath = "C:\Users\The_Prist\Рабочий стол"

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


Все чаще стали появляться вопросы типа "А как отследить изменения только в конкретном диапазоне?". На самом деле не очень сложно. Надо добавить пару строк, которые будут определять в каких ячейках были изменения и какие отслеживать. Только добавить строки надо будет в обеих процедурах: Workbook_SheetChange и Workbook_SheetSelectionChange.
Например, код ниже будет отслеживать только те ячейки, для которых значение изменили только в диапазоне B:F:

Option Explicit
Public sValue As String
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "LOG" Then Exit Sub
    Dim rCells As Range
    'если изменения произошли не в диапазоне "B:F" - ничего не делаем
    On Error Resume Next
    Set rCells = Intersect(Target, Range("B:F"))
    If rCells Is Nothing Then Exit Sub
    On Error GoTo 0
 
    Dim sLastValue As String
    Dim lLastRow As Long, wbLOG As Workbook
    Dim sPath as String
    Const sLOGName As String = "\LOG.txt" '"\LOG.xls"
    sPath = Application.DefaultFilePath
    Application.ScreenUpdating = False
    '==============   только для записи в текстовый файл   ======================
    If Dir(sPath & sLOGName, vbDirectory) = "" Then
        Open sPath & sLOGName For Output As #1: Close #1
    End If
    '==============   только для записи в отдельный файл Excel ======================
'    If Dir(sPath & sLOGName, vbDirectory) = "" Then
'        Set wbLOG = Workbooks.Add
'        wbLOG.SaveAs sPath & sLOGName, xlNormal
'    End If
    Set wbLOG = Workbooks.Open(sPath & sLOGName)
    '============================================================================
    With wbLOG.Sheets(1)
        lLastRow = .Cells.SpecialCells(xlLastCell).Row + 1
        If lLastRow = .Rows.Count Then Exit Sub
        Application.ScreenUpdating = False: Application.EnableEvents = False
        .Cells(lLastRow, 1) = CreateObject("wscript.network").UserName
        .Cells(lLastRow, 2) = Target.Address(0, 0)
        .Cells(lLastRow, 3) = Format(Now, "dd.mm.yyyy HH:MM:SS")
        .Cells(lLastRow, 4) = Sh.Name
        .Cells(lLastRow, 5).NumberFormat = "@"
        .Cells(lLastRow, 5) = sValue
        If rCells.Count > 1 Then
            Dim rCell As Range, rRng As Range
            On Error Resume Next
            Set rRng = Intersect(rCells, Sh.UsedRange): On Error GoTo 0
            If Not rRng Is Nothing Then
                For Each rCell In rRng
                    If Not IsError(Target) Then sLastValue = sLastValue & "," & rCell Else sLastValue = sLastValue & "," & "Err"
                Next rCell
                sLastValue = Mid(sLastValue, 2)
            Else
                sLastValue = ""
            End If
        Else
            If Not IsError(Target) Then sLastValue = Target.Value Else sLastValue = "Err"
        End If
        .Cells(lLastRow, 6).NumberFormat = "@"
        .Cells(lLastRow, 6) = sLastValue
    End With
    wbLOG.Close 1
    Application.ScreenUpdating = True: Application.EnableEvents = True
End Sub
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "LOG" Then Exit Sub
    Dim rCells As Range
    'если изменения произошли не в диапазоне "B:F" - ничего не делаем
    On Error Resume Next
    Set rCells = Intersect(Target, Range("B:F"))
    If rCells Is Nothing Then Exit Sub
    On Error GoTo 0
 
    If rCells.Count > 1 Then
        Dim rCell As Range, rRng As Range
        On Error Resume Next
        Set rRng = Intersect(rCells, Sh.UsedRange): On Error GoTo 0
        If rRng Is Nothing Then Exit Sub
        For Each rCell In rRng
            If Not IsError(rCell) Then sValue = sValue & "," & rCell Else sValue = sValue & "," & "Err"
        Next rCell
        sValue = Mid(sValue, 2)
    Else
        If Not IsError(Target) Then sValue = Target.Value Else sValue = "Err"
    End If
End Sub

Скачать пример

  Tips_Macro_LOG.xls (50,0 KiB, 8 386 скачиваний)

Так же см.:
Выделение сделанных изменений
Запись изменений на листе в примечания


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

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

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

    Посоветуйте
    Ваш макрос отслеживает изменение всей книги сразу (кроме отдельных листов указанных в коде) или только на активном.
    У меня файл с относительно большим количеством листов (более 100) и при изменении значений в каждой ячейке экран заметно подрагивает (ScreenUpdating и др. использую).
    Поможет ли задание конкретных уменьшенных диапазонов для каждого, или всех сразу, листов, например на А1:ВВ200?!

    • Александр, ознакомьтесь с комментариями к статье. Если не изменяет память - отслеживание изменений в конкретном диапазоне было на первой странице(максимум на второй).
      Для работы с объединенными ячейками необходимо изменять код и тестировать. Если хотите сами помучиться - попробуйте с использованием свойства MergeArea.

  2. Александр Медведев:

    Подскажите, ваш макрос отслеживает изменение всей книги сразу (кроме отдельных листов указанных в коде) или только на активном.
    У меня файл с относительно большим количеством листов (более 100) и при изменении значений в каждой ячейке экран заметно подрагивает (ScreenUpdating и др. использую).
    Поможет ли задание конкретных уменьшенных диапазонов для каждого, или всех сразу, листов, например на А1:ВВ200?!

    • Еще раз советую прочесть комментарии. Там есть и про листы, и про диапазоны. Насколько что будет быстрее я не могу сказать. У Вас есть пример с множеством листов - примените советы(прочитав комментарии к статье) и посмотрите - ускорится или нет.
      И не надо восклицательных знаков - я Вам ничем не обязан. Будьте сдержаннее.

  3. Александр Медведев:

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

  4. Михаил:

    Добрый день.
    Спасибо Вам за множество полезного материала. Прошу подсказать решение или где почитать.
    Пытаюсь совместить вашу идею и материал Павлова Николая "Отслеживание входа пользователей в книгу Excel", но ничего не получается.
    Суть идеи вести реестр, журнал и показывать листы данных только при включении макросов.
    Дополнительно пытаюсь сохранить не только реестр изменений ячейки, а параллельно вести журнал в котором при выходе и сохранении будет записываться:
    - сначала срока со старыми значениями ячеек,
    - строка с новыми значениями ячеек
    Проблем всего 2
    1) Ошибка при выполнении Private Sub Workbook_Open() и Private Sub Workbook_BeforeClose(Cancel As Boolean)
    2) Не знаю как в Ваш код добавить копирование строк со старыми и новыми значениями на лист "реестр изменений"
    Пример прилагаю https://drive.google.com/file/d/0B0OCdyjFeVXOem4tRFJGSW1FdGs/view?usp=sharing

  5. Alla:

    Дмитрий, СПАСИБО за Вашу работу, очень помогаете.
    С макросами только начитаю разбираться, поэтому не всегда могу сообразить, как адаптировать Ваш шаблон под свою задачу.
    Помогите, пожалуйста: чтобы изменения заносились не на новый лист LOG, а на лист с исходной таблицей - в крайнюю колонку по изменяемой строке (например, в ячейке A1 произошло изменение -> в ячейку BH1 (крайнюю правую в таблице) будет попадать информация об адресе измененной ячейки (A1).

    • Алла, здесь есть нюанс: как в одну ячейку записать сразу всю информацию, которая записывается на лист LOG? Может подойдет это решение: Запись изменений на листе в примечания

      • Alla:

        Сейчас может глупость напишу, не судите =)
        Если смотреть код с примечаниями, я понимаю, что мне нужно работать со строкой:
        oComment.Text oComment.Text & Chr(10) & CreateObject("wscript.network").UserName & ":" & Chr(10) & "было: " & vv & "; стало: " & v & "; Дата: " & Format(Now, "dd.mm.yy HH:MM")

        Дата, Было-стало, Кто внес изменения - я удалю, мне это не нужно, оставлю только инфо об изменяемой ячейке.
        Мой ступор: как написать код так, чтобы программа вычисляла где изменения и показывала информацию о ячейке в том ряду, где оно прозошло. Т.е в адресе поставлю номер столбца постоянный, а строка должна зависеть от изменения.

        пс. Вы проводите тренинги только для уже сформированных групп? в смысле, можно ли присоединить к группе? =)

        • Строка, в которой произошли изменения: Target.Row. Обратиться к столбцу BH этой строки: Target.parent.Range("BH" & Target.Row)
          Тренинги сейчас провожу исключительно корпоративные, отдельные группы не собираю.

          • Alla:

            Дмитрий спасибо еще раз.
            Если написать строку
            Target.Parent.Range("BH" & Target.Row) = Target.Address
            То все работает, за исключением того, что выводится адрес ячейки с последним изменением, а не адреса всех измененных ячеек в ряду.
            Я думаю, нужно написать что-то типа того, как в коде с Комментариями: если ячейка пустая, то писать адрес изменения, в противном случае - добавлять новое значение адреса к существующему.
            Я пишу:
            Dim cCell As String
            Set cCell = Target.Parent.Range("K" & Target.Row)
            If cCell Is Nothing Then
            Target.Parent.Range("K" & Target.Row) = Target.Address
            Else
            cCell.Text cCell.Text & Target.Parent.Range("K" & Target.Row) = Target.Address
            End If

            Не работает =)

      • Alla:

        Вот как работает (вдруг кому пригодится):
        Option Explicit

        Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Me.Range("B2:H11")) Is Nothing Then Exit Sub
        Dim oComment As Comment
        On Error Resume Next
        If Target.Parent.Range("I" & Target.Row) = "" Then
        Target.Parent.Range("I" & Target.Row) = Target.Address
        Else
        Target.Parent.Range("I" & Target.Row) = Target.Parent.Range("I" & Target.Row) + "," + Target.Address
        End If

        End Sub

  6. Виктория:

    Добрый день! Простите, если что не так, нщ у меня скачивается девственно чистый файл примера. Что я не то делаю? Спасибо.

  7. Дмитрий:

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

  8. Павел:

    Дмитрий, СПАСИБО большое, очень полезный макрос. Подскажите, а возможно, чтобы макрос записывал не все изменения каким-либо пользователем, а только последние, т.е. если один и тот же пользователь делал изменения нескольких ячеек, то указывались данные только по последней? Т.е. по идеи для каждого пользователя в листе LOG была бы только одна строка.

    • Павел, должно быть примерно так. Вместо строки

      Dim rf As Range, sUser As String
          sUser = CreateObject("wscript.network").UserName
          'ищем пользователя в столбце А
          Set rf = .Columns(1).Find(sUser, , xlValues, xlWhole)
          'если нашли - используем для записи эту строку
          If Not rf Is Nothing Then
              lLastRow = rf.Row
          Else 'если не нашли - первую пустую(т.е. новый пользователь)
              lLastRow = .Cells.SpecialCells(xlLastCell).Row + 1
          End If

      вставляете такие строки

      Dim rf As Range, sUser As String
          sUser = CreateObject("wscript.network").UserName
          'ищем пользователя в столбце А
          Set rf = .Columns(1).Find(sUser, , xlValues, xlWhole)
          'если нашли - используем для записи эту строку
          If Not rf Is Nothing Then
              lLastRow = rf.Row
          Else 'если не нашли - первую пустую(т.е. новый пользователь)
              lLastRow = .Cells.SpecialCells(xlLastCell).Row + 1
          End If
  9. Дмитрий:

    Ошибка при создании новой строки с копированием (Ctrl+C - Ctrl+"+")
    Ругается на эту строку
    If Not IsError(Target) Then sLastValue = sLastValue & "," & rCell Else sLastValue = sLastValue & "," & "Err"

    • Дмитрий, да, вполне возможно. Надо дополнять код разными проверками и по хорошему, вообще переработать для более стабильной и правильной работы. Все же коду не один год и нуждается в обновлении. Будет время - обязательно допилю.

  10. Irina:

    Дмитрий, добрый вечер! Вопрос по первому коду из статьи: при добавлении и удалении строки / столбца в Журнале событий все фиксируется, но поля "старое" и "новое значение" пустые. Т.е. нет прописи слова "Удаление столбца" или "Удаление строки" или "Вставка столбца \ строки". Помогите, пожалуйста, в каком месте подправить код?

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

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


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