Excel событие выделение ячейки
Надстройка пригодится тем, кто часто работает с большими таблицами, просматривая их и сопоставляя данные в строках и столбцах. Что дает эта надстройка? Она выделяет столбец и строку таблицы на пересечении выделенной ячейки, благодаря чему можно просмотреть все данные в столбце и строке активной ячейки, не перепроверяя себя лишний раз – "А в том ли столбце я смотрю данные?".
Метод - выбор метода подсветки строки и столбца, их два:
- выделение - строка и столбец выделяются так же, как если бы вы выделяли ячейки мышкой. У этого метода есть один недостаток. Если на листе в этом режиме будут сделаны изменения(удаление значения ячейки), то отменить их будет невозможно
- условное форматирование - строка и столбец выделяются при помощи условного форматирования. Можно выбрать один из шести цветов для подсветки выделения строки и столбца. У такого подхода тоже есть недостаток. Все условное форматирование, примененное к текущему листу ранее, будет удалено
Весь лист - выделяются строка и столбец всего листа.
Вся таблица данных - выделяются строка и столбец только в пределах используемого диапазона данных.
Выбранный диапазон - выделяются строка и столбец только в пределах указанного вами диапазона. Особенность: не допускается выделение несвязанных диапазонов.
Область применения - выбирается область листа, к которой применяется координатное выделение:
- только строка - выделяется только строка активной ячейки
- только столбец - выделяется только столбец активной ячейки
- строка и столбец - выделяется строка и столбец на пересечении активной ячейки
Отменить Координатное выделение - отменяет примененное координатное выделение.
Внимание! Координатное выделение действует во всех открытых книгах во всех листах до тех пор, пока вы не отключите его через пункт «Отменить координатное выделение».
Важно! При примененном координатном выделении невозможно скопировать выделенный диапазон, однако можно удалить значение активной ячейки (будет удалено только значение одной ячейки, расположенной на пересечении строки и столбца).
ExcelVBA_KoordSel.zip (49,5 KiB, 5 012 скачиваний)
В архиве расположен файл надстройки. Прежде чем установить надстройку, необходимо её распаковать из архива на жесткий диск, после чего установить. Как установить надстройку: Установка надстроек
Надстройка распространяется бесплатно и с открытыми исходными кодами - смотрите, изучайте, меняйте под себя.
Самый важный момент для тех, кого не устроит цвет выделения по умолчанию: чтобы изменить цвет выделения ячеек через условное форматирование, необходимо перейти в модуль mKoordSelection, найти вверху строку
Public Const lKS_FC_Color As Long = 10921638
и заменить число 10921638 на числовой код нужного цвета заливки. Подобрать нужный цвет можно следующим образом:
- назначаем заливке любой ячейки нужный цвет
- выделяем эту ячейку и выполняем код:
Sub GetActiveCellColor() MsgBox ActiveCell.Interior.Color, vbInformation, sAPP_NAME End Sub
этот код так же есть внутри надстройки, поэтому его можно вызвать просто через Alt+F8
Изменить цвет выделения методом Обычного выделения нельзя. Это ограничение самого Excel. Можно изменить только изменением цветовых схем Windows.
Иногда при разработке надстройки просто необходимо отследить какое-либо событие в книге. Но модуль ЭтаКнига и модули листов надстройки позволяют отследить лишь те события, которые происходят в той книге, в которой этот код прописан. А как же другие книги? Как, например, отследить событие открытия любой книги в 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
А процедура ниже поможет отследить изменение значений ячеек во всех открытых книгах и отменить нежелательные изменения:
В приложенном к статье файле будет чуть более понятно что делает эта процедура.
Естественно, в таких процедурах можно назначить выполнение и других(нужных) действий. Например, вызов макроса ( Call ИмяМакроса ). Макрос в таком случае должен быть размещен в стандартном модуле и иметь статус Public (или вовсе без статуса). Сам модуль должен тоже находится в той же книге.
Предположим есть общий файл, в который вы забиваете исходные данные и отсылаете другим людям. Но когда вам этот файл присылают обратно - неплохо бы знать в какие ячейки были внесены данные, чтобы люди при этом не выделяли эти ячейки сами каждый раз каким-нибудь цветом (пусть будет красный ). Приведенный ниже код выделяет ячейку как только значение или формула в ней были изменены.
Единственное, о чем хочу сразу предупредить - код реагирует на изменение только одной ячейки. Если разом было изменено более одной ячейки(например, выделили столбец и удалили оттуда все значения) - такие изменения код проигнорирует. Так же код отслеживает только те изменения, которые были сделаны вручную. Изменения ячеек формулами так же игнорируются. Для подобного отслеживания нужно будет приложить побольше усилий.
И так же нужно выполнить одно из важных условий: макросы должны быть разрешены. Иначе никакие изменения выделяться не будут.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'если изменили более одной ячейки - ничего не отслеживаем Dim lcnt As Long 'игнорируем ошибки на случай, если выделено слишком много ячеек для помещения в Long On Error Resume Next lcnt = Target.Count If lcnt > 1 Then Exit Sub On Error GoTo 0 Dim vOldVal, vNewVal, sSel As String With Application 'отключаем отслеживание событий .EnableEvents = False .ScreenUpdating = False 'запоминаем текущее выделение ячеек sSel = Selection.Address 'запоминаем текущее значение vNewVal = Target.Formula 'возвращаем предыдущее значение .Undo 'запоминаем предыдущее значение vOldVal = Target.Formula 'возвращаем текущее значение Target.Formula = vNewVal 'если значение/формула изменились окрашиваем в красный цвет If vOldVal <> vNewVal Then Target.Interior.Color = vbRed End If 'запоминаем текущее выделение ячеек Me.Range(sSel).Select 'возвращаем отслеживание событий .EnableEvents = True .ScreenUpdating = True End With End Sub
Как это использовать. Щелкаем правой кнопкой мыши на ярлыке того листа, изменения в котором необходимо изменить -Исходный текст (View code) -Вставляем приведенный код. Подробнее про модули листа см.здесь.
Но приведенный код работает только в одном листе(том, в модуле которого размещен код). Если необходимо отследить изменения во всех листах книги, то можно продублировать код в каждый лист, но если листов много, то это довольно утомительно. Поэтому для таких целей можно использовать следующий код, который необходимо поместить уже не в модуль листа, в модуль книги:
Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'если изменили более одной ячейки - ничего не отслеживаем Dim lcnt As Long 'игнорируем ошибки на случай, если выделено слишком много ячеек для помещения в Long On Error Resume Next lcnt = Target.Count If lcnt > 1 Then Exit Sub On Error GoTo 0 Dim vOldVal, vNewVal, sSel As String With Application 'отключаем отслеживание событий .EnableEvents = False .ScreenUpdating = False 'запоминаем текущее выделение ячеек 'но только если они на активном листе If Sh.Name = Target.Worksheet.Name Then sSel = Selection.Address End If 'запоминаем текущее значение vNewVal = Target.Formula 'возвращаем предыдущее значение .Undo 'запоминаем предыдущее значение vOldVal = Target.Formula 'возвращаем текущее значение Target.Formula = vNewVal 'если значение/формула изменились окрашиваем в красный цвет If vOldVal <> vNewVal Then Target.Interior.Color = vbRed End If 'возвращаем текущее выделение ячеек 'но только если они на активном листе If Sh.Name = Target.Worksheet.Name Then Sh.Range(sSel).Select End If 'возвращаем отслеживание событий .EnableEvents = True .ScreenUpdating = True End With End Sub
Повторюсь, что этот код должен быть вставлен в модуль книги. Что такое модуль книги и где он расположен лучше подсмотреть здесь: где искать модуль книги.
Обращаю особое внимание, что в данном случае коды отслеживания изменения из конкретных листов лучше удалить, если не преследуется каких-то конкретных целей. Иначе есть шанс получить ошибку(т.к. будет неоднократный вызов методов).
Если изменения надо отслеживать во всех листах, кроме какого-то одного(например, листа с именем "Лист3" ), то код можно записать так:
Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "Лист3" Then Exit Sub End If 'если изменили более одной ячейки - ничего не отслеживаем Dim lcnt As Long 'игнорируем ошибки на случай, если выделено слишком много ячеек для помещения в Long On Error Resume Next lcnt = Target.Count If lcnt > 1 Then Exit Sub On Error GoTo 0 Dim vOldVal, vNewVal, sSel As String With Application 'отключаем отслеживание событий .EnableEvents = False .ScreenUpdating = False 'запоминаем текущее выделение ячеек 'но только если они на активном листе If Sh.Name = Target.Worksheet.Name Then sSel = Selection.Address End If 'запоминаем текущее значение vNewVal = Target.Formula 'возвращаем предыдущее значение .Undo 'запоминаем предыдущее значение vOldVal = Target.Formula 'возвращаем текущее значение Target.Formula = vNewVal 'если значение/формула изменились окрашиваем в красный цвет If vOldVal <> vNewVal Then Target.Interior.Color = vbRed End If 'возвращаем текущее выделение ячеек 'но только если они на активном листе If Sh.Name = Target.Worksheet.Name Then Sh.Range(sSel).Select End If 'возвращаем отслеживание событий .EnableEvents = True .ScreenUpdating = True End With End Sub
Если игнорировать надо более одного листа, то можно дописать нужные листы таким образом:
If Sh.Name = "Лист3" Or Sh.Name = "Лист6" Then
т.е. через оператор Or записывать сравнение имени листа.
Все чаще стали появляться вопросы типа "А как отследить изменения только в конкретном диапазоне?". На самом деле не очень сложно. Например, код ниже будет выделять только те ячейки, для которых значение изменили только в диапазоне B:F :
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'если изменили более одной ячейки - ничего не отслеживаем Dim lcnt As Long 'игнорируем ошибки на случай, если выделено слишком много ячеек для помещения в Long On Error Resume Next lcnt = Target.Count If lcnt > 1 Then Exit Sub On Error GoTo 0 Dim vOldVal, vNewVal, sSel As String 'если изменения произошли в диапазоне "B:F" - подсвечиваем If Not Intersect(Target, Range("B:F")) Is Nothing Then With Application 'отключаем отслеживание событий .EnableEvents = False .ScreenUpdating = False 'запоминаем текущее выделение ячеек sSel = Selection.Address 'запоминаем текущее значение vNewVal = Target.Formula 'возвращаем предыдущее значение .Undo 'запоминаем предыдущее значение vOldVal = Target.Formula 'возвращаем текущее значение Target.Formula = vNewVal 'если значение/формула изменились окрашиваем в красный цвет If vOldVal <> vNewVal Then Target.Interior.Color = vbRed End If 'возвращаем текущее выделение ячеек Me.Range(sSel).Select 'возвращаем отслеживание событий .EnableEvents = True .ScreenUpdating = True End With End If End Sub
Синтаксис процедуры, выполнение которой инициируется событием Worksheet.SelectionChange:
Private Sub Worksheet_SelectionChange ( ByVal Target As Range )Эта процедура VBA должна быть размещена в модуле рабочего листа Excel, выбор диапазона ячеек которого должен инициировать ее запуск.
Шаблон процедуры можно скопировать и вставить в модуль рабочего листа, но не обязательно. Если открыть модуль нужного листа, выбрать в левом верхнем поле объект Worksheet , шаблон процедуры будет добавлен автоматически:
У объекта Worksheet есть и другие события, которые можно выбрать в правом верхнем поле модуля рабочего листа. Процедура с событием SelectionChange добавляется по умолчанию.
Примеры кода с Worksheet.SelectionChange
Пример разработчика
Замечательный пример дан на сайте разработчика:
Private Sub Worksheet_SelectionChange ( ByVal Target As Range )При выборе на листе любого диапазона, в том числе отдельной ячейки, лист автоматически прокручивается по горизонтали и вертикали, пока выделенный диапазон не окажется в верхнем левом углу экрана.
Эта процедура работает и при выборе ячейки через адресную строку (слева над обозначениями столбцов), и при выборе из кода VBA Excel, например:
Выбор одной отдельной ячейки
Инициируем выполнение основных операторов процедуры с событием Worksheet.SelectionChange выбором одной отдельной ячейки:
Private Sub Worksheet_SelectionChange ( ByVal Target As Range )Основной оператор MsgBox "Выбрана ячейка E5" будет выполнен при выборе ячейки E5.
Примечание:
В условии примера используется свойство Address переменной Target , так как в прямом выражении Target = Range("E5") по умолчанию сравниваются значения диапазонов. В результате этого, при выборе другой ячейки со значением, совпадающим со значением ячейки E5, равенство будет истинным и основные операторы будут выполнены, а при выборе более одной ячейки, будет сгенерирована ошибка.
Выбор диапазона с заданной ячейкой
Выполнение основных операторов процедуры при вхождении заданной ячейки в выбранный диапазон:
Private Sub Worksheet_SelectionChange ( ByVal Target As Range ) If Not Intersect ( Target , Range ( "B3" ) ) Is Nothing ThenОсновной оператор MsgBox "Ячейка B3 входит в выбранный диапазон" будет выполнен при выделении диапазона, в который входит ячейка B3, в том числе и при выделении одной этой ячейки.
Выбор ячейки в заданной строке
Инициируем выполнение основных операторов процедуры с событием Worksheet.SelectionChange выбором любой отдельной ячейки во второй строке:
Private Sub Worksheet_SelectionChange ( ByVal Target As Range )Дополнительный оператор If Target.Count > 1 Then Exit Sub необходим для выхода из процедуры при выделении более одной ячейки. Причина: при выделении произвольного диапазона, ограниченного сверху второй строкой, выражение Target.Row = 2 будет возвращать значение True , и операторы в блоке If . End If будут выполнены.
Ввод даты в ячейку первого столбца
Автоматическое добавление текущей даты в выбранную ячейку первого столбца при условии, что предыдущая ячейка сверху не пустая, а ячейка снизу – пустая:
Private Sub Worksheet_SelectionChange ( ByVal Target As Range ) If Target . Count > 1 Or Target . Row = 1 Or Target . Row = ActiveSheet . Rows . Count Then Exit Sub If Target . Column = 1 And Target . Offset ( - 1 , 0 ) <> "" And Target . Offset ( 1 , 0 ) = "" ThenЭтот код 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».
Читайте также: