Как отследить событие(например выделение ячеек) в любой книге?
Иногда при разработке надстройки просто необходимо отследить какое-либо событие в книге. Но модуль ЭтаКнига и модули листов надстройки позволяют отследить лишь те события, которые происходят в той книге, в которой этот код прописан. А как же другие книги? Как, например, отследить событие открытия любой книги в 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 |
Теперь при открытии любой книги будет появляться сообщение с именем именно открытой книги.
Сам по себе код не заработает. Т.к. назначение значения переменной 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 |
Таким образом у нас есть две переменные, которые мы можем использовать. Например, можно производить определенные действия только на листах с конкретным именем:
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 |
В приложенном к статье файле будет чуть более понятно что делает эта процедура.
Естественно, в таких процедурах можно назначить выполнение и других(нужных) действий. Например, вызов макроса (
Tips_Macro_How_Catch_Events.xls (60,5 KiB, 4 782 скачиваний)
Также см.:
Что такое переменная и как правильно её объявить?
Статья помогла? Поделись ссылкой с друзьями!
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Супер. очень доходчиво, понятно. То что искал. Ставлю +1 в гугл плюс
Всё это хорошо, но при необходимости повторного выбора этой же ячейки событие не происходит и, естественно, программа выполнятся не будет (проверено). Что можете посоветовать в этом случае. я не проверяю другие книги, но в открытой нужно выбрать конкретную ячейку и обработать содержимое, в результате чего содержимое может изменится. Я использую следующую конструкцию:
И для того чтобы снова выбрать ту же ячейку приходится заводить незначащие ячейки, чтобы выбрав их иметь возможность вернутся к предыдущему выбору.
Очень уж мудрёно получилось, но надеюсь понятно.
Ничего не посоветую, т.к. события не произойдет, если ячейка уже выделена. Может имеет смысл присмотреться к событию Worksheet_Change, раз содержимое обрабатываете без выхода из ячейки?
Ну, и как же сделать РАБОЧЕЕ событие для отслеживания события при свёртывании приложения Excel?
Imort, для начала задать вопрос не "ну, и как же" - здесь Вам никто ничего не должен. После чего прочитать внимательно статью и сделать как написано. И просмотреть все доступные события. Может тогда найдете событие WindowResize. И найдете у передаваемого объекта Wn свойство WindowState.
Спасибо, отличный макрос
Ни auto_open на листе, ни Workbook_Open на книге не срабатывают. Как еще можно запустить макрос автоматически?
Игорь, давайте поменьше категоризма и побольше конкретики. Кто сказал, что auto_open должен быть на листе? Эта процедура размещается в стандартном модуле, т.к. она использовалась в те лихие времена, когда событийные процедуры листов и книг не встраивались в листы и книги.
Не работает только у Вас. Значит и дело в Вас или Вашем ПК, Excel. Опишите подробно свои действия - как что и куда записываете, как проверяете работу. Включены ли макросы вообще.
Дмитрий, прошу помощи)
У меня есть форма - нажимая на кнопку открывается нужный мне файл Excel, а форма сворачивается.
Как сделать так, чтобы после закрытия файла - форма опять отображалась?
Private WithEvents App_calc As Application
Sub Workbook_Open()
Set App_calc = Application
End Sub
Private Sub App_calc_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If Wb.Name = "123_456_3.xlsm" Then
Application.EnableEvents = True
Cancel = False
F_Main_NEW.Show
End If
End Sub
Так не получается - форма открывается, а файл не закрывается, пока форму не закроешь :)
У Вас главная проблема в том, что форма показывается в модальном режиме. Это означает, что после показа формы дальнейшее выполнение кода в той процедуре, из которой форма вызвана, приостанавливается до тех пор, пока форма не будет закрыта.
Поэтому варианта два:
1. Отображать форму не модально: F_Main_NEW.Show 0
2. Отображать форму с запозданием. Создаете отдельную процедуру:
а в процедуре WorkbookBeforeClose вызывать уже через Application.OnTime эту процедуру:
Тогда сначала книга будет закрыта, а потом уже отображена форма.
Дмитрий, добрый день!
Подскажите, пожалуйста.Хочу чтобы при сохранении файла в определенном листе фиксировалась дата сохранения(так сказать журнал изменений файла). Т.е. прописываю нужный мне код на Sub Workbook_AfterSave. Отрабатывает отлично. Но на случай сохранения файла через закрытие и появления диалогового окна о сохранении, я добавил этот же код на событие Workbook_BeforeClose. но к сожалению при нажатии пользователем на кнопку отмены - скрипт уже отработал. Есть ли возможность отловить эти кнопки на диалоговом окне сохранения? Заранее благодарю за помощь.
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
With Sheets(3)
lLastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range("A" & lLastRow).Value = Now
.Range("B" & lLastRow).Value = Application.UserName
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="999999"
With Sheets(3)
lLastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range("A" & lLastRow).Value = Now
.Range("B" & lLastRow).Value = Application.UserName
End With
If Cancel Then Sheets(1).Unprotect Password:="999999"
End Sub
Sub Workbook_Open()
Sheets(1).Unprotect Password:="999999"
End Sub