Lost your password?


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

Как отследить событие(например выделение ячеек) в любой книге?

Иногда при разработке надстройки просто необходимо отследить какое-либо событие в книге. Но модуль ЭтаКнига и модули листов надстройки позволяют отследить лишь те события, которые происходят в той книге, в которой этот код прописан. А как же другие книги? Как, например, отследить событие открытия любой книги в Excel и сделать какое-то действие в зависимости от имени открытой книги? Или как отследить выделение ячейки в любой книге? Изменение значений ячеек?

Если не знаете что такое надстройка - Как создать свою надстройку?

На самом деле все до смешного просто:
В модуле ЭтаКнига главной книги(надстройка либо PERSONAL.XLS) необходимо создать переменную, которая будет ссылкой на все приложение Excel

Private WithEvents App As Application

На событие открытия главной книги (той, в которой пишется код и в которой объявили переменную App - опять же это надстройка либо PERSONAL.XLS) присваиваем этой переменной App значение запущенного приложения Excel:

Private Sub Workbook_Open()
    Set App = Application
End Sub

Т.е. мы теперь имеем как бы свою локальную управляемую ссылку на Excel. Это позволит нам получить доступ к событиям приложения Excel из VBA и отследить их. И среди прочих событий есть такие, которые относятся ко всем открытым книгам. Т.е. то же выделение ячеек мы сможем обработать только внутри своей надстройки, но срабатывать оно будет при выделении ячеек в любой открытой книге.
Теперь создаем непосредственно событие - аналогично выбору других событий в книге в левом окне выбора объектов выбираем App. В правом появятся все доступные события для нашего объекта App:

в этом окне перечислены все события, которые могут быть "перехвачены" в любой открытой книге, а не только той, в которой этот код записан. Сразу после выбора какого-либо события из списка автоматически будет создана пустая процедура, в которую надо будет лишь добавить необходимый код.
Рассмотрим некоторые из этих событий.

Вот так, например, будет выглядеть код отслеживания открытия любой книги:

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "Вы открыли книгу:" & Wb.Name
End Sub

Теперь при открытии любой книги будет появляться сообщение с именем именно открытой книги.
Wb - это переменная событийной процедуры. Для каждой процедуры они могут отличаться или вовсе отсутствовать. Но если они есть - значит можно их использовать. Например, в этой процедуре(App_WorkbookOpen) Wb это открываемая книга, т.е. объект типа Workbook. И к ней можно обращаться как к любой книге: перебрать листы, изменить какие-то свойства и т.п. Например, в коде выше я просто вывожу в информационное окно с именем открываемой книги.

Сам по себе код не заработает. Т.к. назначение значения переменной App происходит только при открытии самой книги(надстройки или PERSONAL), то после создания кодов надо будет сохранить эту книгу и открыть заново

А с помощью этого кода можно отследить создание новой книги:

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    MsgBox "Вы создали новую книгу"
End Sub

Отслеживаем выделение ячеек во всех открытых книгах:

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "Вы выделили ячейку с адресом: " & Target.Address
End Sub

Target - это объект Range(ячейка или диапазон ячеек), которые были выделены в книге.
Sh - это объект Worksheet, ячейки которого были выделены.
Таким образом у нас есть две переменные, которые мы можем использовать. Например, можно производить определенные действия только на листах с конкретным именем:

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Отчет" Then
        MsgBox "Вы выделили ячейку с адресом: " & Target.Address
    End If
End Sub

А процедура ниже поможет отследить изменение значений ячеек во всех открытых книгах и отменить нежелательные изменения:

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim bUndo As Boolean
    If Sh.Name <> "Для изменений" Then
        If Sh.Name = "Описание" Then 'для этого листа можно изменять только ячейки диапазона "A16:B20"
            If Intersect(Target, Sh.Range("A16:B20")) Is Nothing Then
                MsgBox "На этом листе изменять можно только ячейки в диапазоне 'A16:B20'!", vbCritical, "www.excel-vba.ru"
                bUndo = True
            End If
        Else 'для всех других листов, кроме листа "Для изменений" - изменять значения ячеек вообще нельзя
            MsgBox "Ячейки на этом листе нельзя изменять!", vbCritical, "www.excel-vba.ru"
            bUndo = True
        End If
        If bUndo Then
            With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = True
            End With
        End If
    End If
End Sub

В приложенном к статье файле будет чуть более понятно что делает эта процедура.

Естественно, в таких процедурах можно назначить выполнение и других(нужных) действий. Например, вызов макроса (Call ИмяМакроса). Макрос в таком случае должен быть размещен в стандартном модуле и иметь статус Public(или вовсе без статуса). Сам модуль должен тоже находится в той же книге.

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

  Tips_Macro_How_Catch_Events.xls (60,5 KiB, 4 782 скачиваний)

Также см.:
Что такое переменная и как правильно её объявить?


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

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

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

    Здравствуйте!
    А есть возможность отслеживать событие закрытия книги?
    Например, мне нужно изменить размер пустого окна Excel (после закрытия всех книг в нём).
    И ещё - можно применить всё то же самое к ворду?
    Спасибо!

  2. Руслан:

    Дмитрий добрый день. Спасибо Вам, за прекрасный метод контроля ввода данных в необходимый диапазон. Я добавил в Ваш код, диапазон If Intersect(Target, Sh.Range("A:G", "J:J", "L:L", "P:P", "T:T", "V:V", "X:X", "Z:Z", "AB:AB", "AD:AD", "AF:AF", "AG:AG", "AK:AK", "AM:AM", "AO:AO", "AQ:AQ", "AS:AS", "AU:AU", "AW:AX", "BB:BB", "BD:BD", "BF:BF", "BH:BH", "BJ:BJ", "BL:BL", "BN:BO", "BS:BS", "BU:BU", "BW:BW", "BY:BY", "CA:CA", "CC:CC", "CE:CE")) Is Nothing Then и получаю ошибку "wrong number of arguments or invalid property assignment" Подскажите пожалуйста, как её исправить? Спасибо

    • Руслан, тут проблема в Вашем непонимании обращения к диапазонам. Если разделяете запятыми - то это делать надо внутри одного текстового блока. На примере ТРЕХ адресов. Вместо

      Sh.Range("A:G", "J:J", "L:L")

      надо записать так:

      Sh.Range("A:G,J:J,L:L")

      дальше, думаю понятно, что делать.

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

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


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