Excel событие выделение ячейки
Здравствуйте! У Вас нет темы по работе с фильтром. Можно проконсультироваться на этот счёт?
Здравствуйте, Сергей!
Задавайте вопрос, постараюсь ответить.
Спасибо, Евгений! Имеется в Excel столбец с включённым автофильтром. Можно ли программно запустить окно фильтра для выбора значений? Т.е. не руками нажимать на иконку фильтра, а заставить окно открываться с помощью макроса. Спасибо за ответ.
Да, это возможно:
Сергей, уточните вопрос: нужно программно нажать кнопки Ok и Отмена или отследить, какая из них была нажата?
Просто отследить, какая кнопка нажата.
По предыдущему вопросу. Я вставил sendkeys… в конец макроса, который запускается при нажатии кнопки на листе. Окно фильтра появляется и тут же закрывается. А как сделать, чтобы окно осталось, и пользователь мог выбрать данные?
Евгений, я разобрался, почему не выводилось окно с фильтром.
Пример отслеживания нажатий кнопки «OK» автофильтра. На нажатие кнопки «Отмена» код не реагирует.
Вставьте в стандартный модуль (в примере — Module1) объявление глобальной переменной и код процедуры для кнопки:
Range("A1").CurrentRegion можно заменить на имя таблицы.
Евгений, а формулу в поле B2 оставлять или её можно удалить?
Можно удалить, если в таблице есть другие формулы. Я изменил код процедуры Worksheet_Calculate() в предыдущем примере, чтобы она не реагировала на изменение формул, а только на нажатие кнопки «OK» автофильтра. Range("A1") — это ячейка с кнопкой фильтра.
Евгений, спасибо за корректировку процедуры. Думаю, в ней и поле myString1 необязательно (и связанная проверка с ним), т.к. пользователь может выбрать все значения.
Но… после команды SendKeys "%" событие Worksheet_Calculate() отрабатывается только один раз :(.
Сергей, событие Worksheet_Calculate() не зависит от команды SendKeys "%" . Ограничение стоит здесь:
Код в процедуре Worksheet_Calculate() срабатывает только когда активна ячейка Range("A1") . Если заменить в этой строке Range("A1") на диапазон строки заголовков таблицы, тогда код будет срабатывать при любой активной ячейке в заголовке.
В переменную myString записывается состояние таблицы до применения фильтра, в переменную myString1 — после применения. Затем их содержимое сравнивается: если они содержат разные значения — значит, фильтр был применен.
Здравствуйте! может я не туда пишу. вопрос
нужно посчитать кол-во строк в столбце «В» (от 100 до 5000) и вставить в формулу вместо 744
table = Range("B2:C744")
спасибо.
В столбце «B» не должно быть пустых ячеек до последней строки таблицы.
Как определить в VBA есть узор в ячейке?
Спасибо за достаточно полную информацию.
Был бы очень вам признателен, если бы вы подсказали:
как изменить ширину блоков верхнего колонтитула.
«Введена слишком длинная строка. Уменьшите число знаков.»
При изменении шрифта с полужирного на обычный всё нормально
воспринимается,
Прошу прощения, если не по адресу.
Добрый день! Никак не могу решить проблему с заблокированным автофильтром на защищенном макросом (нашел на форумах) листе. Подскажите пожалуйста, куда и какую строчку нужно добавить в макрос, чтобы при открытии файла был активен автофильтр:
Добрый день, Владислав!
Замените строку
Добрый день, Евгений!
ОГРОМНОЕ СПАСИБО. :))
Здравствуйте, помогите, пожалуйста, решить задачу((
Разработать программу «Кредитный калькулятор» для расчета выплат по кредиту в условиях начисления процентов по аннуитетной схеме постнумерандо. Форма должна предусматривать ввод:
1) сумма кредита, допустимы 2 знака после запятой;
2) процентная ставка (годовая), допустимы 2 знака после запятой;
3) количество периодов (месяцев), целое положительное число.
Также форма должна содержать две кнопки:
1) «Ok» (выполнить расчет, заполнить лист Excel, освободить форму и закончить работу программы);
2) «Отмена» (освободить форму и закончить работу программы).
На лист Excel следует вывести 5 колонок:
1) номер периода;
2) остаток кредита;
3) сумма процентов за пользование кредитом, подлежащая к оплате;
4) сумма погашения основного долга (кредита);
5) общая сумма выплат за период.
Форма должна быть открыта из макроса «main» и там же освобождена по завершению своего использования. Форма должна содержать только код работы с формой и ввода данных. Логика верификации данных должна быть, по возможности, отделена от кода формы. Бизнес-логика обязательно должна быть отделена от кода формы.
Здравствуйте, Рафия!
С разработкой кредитного калькулятора вам помогут только за плату на бирже фриланса.
Добрый день, Евгений! Я только начинаю работать с VBA. Помогите, пожалуйста, прописать код для автоматического перемещения курсора на ячейку вправо после заполнения предыдущей.
Здравствуйте, Ева! Для перемещения фокуса в ячейку справа код VBA не нужен: используйте для этого клавишу «Tab» или настройте клавишу «Enter» для перехода вправо (Файл >> Параметры >> Дополнительно).
Спасибо за ответ! Да, но это надо делать вручную; речь идёт об автоматическом перемещении курсора.
Иногда при разработке надстройки просто необходимо отследить какое-либо событие в книге. Но модуль ЭтаКнига и модули листов надстройки позволяют отследить лишь те события, которые происходят в той книге, в которой этот код прописан. А как же другие книги? Как, например, отследить событие открытия любой книги в 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
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 493 скачиваний)
Иногда при разработке надстройки просто необходимо отследить какое-либо событие в книге. Но модуль ЭтаКнига и модули листов надстройки позволяют отследить лишь те события, которые происходят в той книге, в которой этот код прописан. А как же другие книги? Как, например, отследить событие открытия любой книги в 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
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 493 скачиваний)
Иногда при разработке надстройки просто необходимо отследить какое-либо событие в книге. Но модуль ЭтаКнига и модули листов надстройки позволяют отследить лишь те события, которые происходят в той книге, в которой этот код прописан. А как же другие книги? Как, например, отследить событие открытия любой книги в 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
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 493 скачиваний)
Синтаксис процедуры, выполнение которой инициируется событием Worksheet.SelectionChange:
Эта процедура VBA Excel запускается при смене на рабочем листе выделенного диапазона (SelectionChange). Она должна быть размещена в модуле рабочего листа Excel, смена выбранного диапазона ячеек которого будет инициировать ее запуск.
Шаблон процедуры можно скопировать и вставить в модуль рабочего листа, но не обязательно. Если открыть модуль нужного листа, выбрать в левом верхнем поле объект Worksheet , шаблон процедуры будет добавлен автоматически:
У объекта Worksheet есть и другие события, которые можно выбрать в правом верхнем поле модуля рабочего листа. Процедура с событием SelectionChange добавляется по умолчанию.
Примеры кода с Worksheet.SelectionChange
Пример разработчика
Замечательный пример дан на сайте разработчика:
При выборе на листе любого диапазона, в том числе отдельной ячейки, лист автоматически прокручивается по горизонтали и вертикали, пока выделенный диапазон не окажется в верхнем левом углу экрана.
Эта процедура работает и при выборе ячейки через адресную строку (слева над обозначениями столбцов), и при выборе из кода VBA Excel, например:
Выбор одной отдельной ячейки
Инициируем выполнение основных операторов процедуры с событием Worksheet.SelectionChange выбором одной отдельной ячейки:
Основной оператор MsgBox "Выбрана ячейка E5" будет выполнен при выборе ячейки E5.
Примечание:
В условии примера используется свойство Address переменной Target , так как в прямом выражении Target = Range("E5") по умолчанию сравниваются значения диапазонов. В результате этого, при выборе другой ячейки со значением, совпадающим со значением ячейки E5, равенство будет истинным и основные операторы будут выполнены, а при выборе более одной ячейки, будет сгенерирована ошибка.
Выбор диапазона с заданной ячейкой
Выполнение основных операторов процедуры при вхождении заданной ячейки в выбранный диапазон:
Основной оператор MsgBox "Ячейка B3 входит в выбранный диапазон" будет выполнен при выделении диапазона, в который входит ячейка B3, в том числе и при выделении одной этой ячейки.
Выбор ячейки в заданной строке
Инициируем выполнение основных операторов процедуры с событием Worksheet.SelectionChange выбором любой отдельной ячейки во второй строке:
Дополнительный оператор If Target.Count > 1 Then Exit Sub необходим для выхода из процедуры при выделении более одной ячейки. Причина: при выделении произвольного диапазона, ограниченного сверху второй строкой, выражение Target.Row = 2 будет возвращать значение True , и операторы в блоке If . End If будут выполнены.
Ввод даты в ячейку первого столбца
Автоматическое добавление текущей даты в выбранную ячейку первого столбца при условии, что предыдущая ячейка сверху не пустая, а ячейка снизу – пустая:
Этот код VBA может быть полезен при ведении реестра, базы данных на листе Excel с записью текущей даты в первой колонке.
Условие If Target.Count > 1 Or Target.Row = 1 Or Target.Row = ActiveSheet.Rows.Count Then Exit Sub завершает процедуру при выборе более одной ячейки, при выборе ячейки A1 и при выборе последней ячейки первого столбца.
Выбор ячейки A1 приводит к ошибке при проверке условия Target.Offset(-1, 0) <> "" , так как происходит выход за границы диапазона рабочего листа.
Ошибка выхода за пределы рабочего листа происходит и при проверке условия Target.Offset(1, 0) = "" , если выбрать последнюю ячейку первой колонки.
Примечание:
Текущая дата будет введена в следующую пустую ячейку первого столбца при переходе к ней от заполненной в том числе нажатием клавиши «Enter».
Пример без отслеживания Target
Если необходимо, чтобы процедура запускалась при любой смене выделенного диапазона, аргумент Target можно не отслеживать:
Читайте также: