Пометка элементов списка флажками
Галочки, они же флажки, они же чекбоксы и в особо запущенных случаях даже крыжики - всё это разные названия для одного простого, но очень полезного объекта, позволяющего удобно отмечать сделанные задачи, выделять элементы различных списков, играть роль бинарного переключателя "вкл-выкл" и т.д. Все вы с ними, конечно же, знакомы.
В этой статье мы рассмотрим несколько способов создания таких флажков-галочек в Microsoft Excel - для новых и старых версий Excel, соответственно. А в конце разберем реальную задачу, где эти чекбоксы можно применить.
Способ 1. Встроенные флажки в новых версиях Excel
Начнем с самого простого и красивого варианта. Если вы счастливый обладатель последних версий Excel, то проблем не будет - на вкладке Вставка недавно появилась кнопка Флажок (Insert - Checkbox):
Принципы работы просты:
- Если выделить несколько ячеек, то вставится несколько флажков, соответственно.
- Размер и дизайн флажков управляются теми же способами, что и шрифт.
- Если выделить ячейку с флажком и повключать-повыключать его, глядя в строку формул, то можно увидеть как в ячейке появляются значения ИСТИНА или ЛОЖЬ. Т.е. такой флажок по своей сути - лишь внешний красивый формат, а на самом деле мы можем работать с этой ячейкой как с обычной, считывая из неё содержимое и анализируя его потом в расчётах (например, подсчитать количество ИСТИН или проверить с помощью функции ЕСЛИ и что-то сделать далее в зависимости от включенности и т.д.)
- Неочевидная особенность такого флажка состоит в том, что если вы захотите от него избавиться (в смысле не выключить, а удалить полностью), то первое нажатие на клавишу Delete его сначала выключит и только второе - удалит. Если же вы захотите убрать флажки, но оставить их значения (ИСТИНА и ЛОЖЬ), то лучше использовать команду Главная - Очистить - Очистить формат (Home - Clear - Clear formatting).
Способ 2. Элемент управления + VBA
Если вы работаете с версией Excel, где встроенных флажков еще нет, то это не повод отчаиваться - их можно легко имитировать с помощью простого макроса, для чего нам потребуется вкладка Разработчик (Developer). Если у вас её не видно, то отобразить её можно через Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon).
Суть этого способа состоит в использовании элемента управления Флажок (Checkbox) в выпадающем списке Вставить (Insert) на вкладке Разработчик (Developer). Если его выбрать, то затем можно в любом месте листа нарисовать прямоугольник, который и будет флажком с подписью. Подпись можно легко поменять и даже, при желании, удалить совсем.
Останется только связать вставленный флажок с ячейкой, чтобы в неё выводились значения ИСТИНА или ЛОЖЬ в зависимости от состояния галочки. Для этого щёлкнем по чекбоксу правой кнопкой мыши и выберем команду Формат объекта (Format control), а затем в открывшемся окне используем поле Связь с ячейкой (Linked cell). Здесь можно задать совершенно любую ячейку - даже ячейку с другого листа (что невозможно для 1-го способа), либо ту же ячейку, где мы нарисовали сам флажок.
У такого способа есть 2 заметных минуса:
Во-первых, теперь необходимо сохранять наш файл в формате с поддержкой макросов (xlsm) и все неизбежные сложности с этим связанные (предупреждения при открытии книги, блокировка макросов по требованиям безопасности в некоторых компаниях и т.д.). Се ля ви.
Во-вторых, подобным образом, конечно, легко можно вставить 1-2 флажка, но что делать, если их нужно много - например для множества этапов сложного проекта или большого списка задач?
Тут нам на помощь в очередной раз придёт простой макрос.
Откроем редактор Visual Basic на вкладке Разработчик, создадим новый пустой модуль через меню Insert - Module и вставим туда следующий код:
Sub AddCheckbox()
For Each cell In Selection
With cell
L = .Left
T = .Top
W = .Width
H = .Height
.NumberFormat = ";;;"
End With
ActiveSheet.CheckBoxes.Add(L + W / 2 - H / 2, T, H, H).Select
With Selection
.Value = xlOn
.LinkedCell = cell.Address
.Characters.Text = ""
End With
Next cell
End Sub
Здесь:
- цикл For Each cell in Selection - перебирает все выделенные ячейки (ссылка на очередную перебираемую ячейку будет в переменной cell)
- в переменные L, T, W, H мы считываем размеры очередной ячейки и меняем формат ячейки на ",,,", чтобы слова ИСТИНА и ЛОЖЬ было видно только в строке формул, но не в самой ячейке
- добавляем чекбокс, включаем его, привязываем к текущей ячейке и убираем его текст
Чтобы быстро удалить все флажки, можно использовать макрос:
Sub DeleteAllCheckboxes() For Each cb in ActiveSheet.CheckBoxes cb.Delete Next cb End Sub
Способ 3. Имитация флажка символом шрифта
Ещё один вариант - использовать вместо полноценного графического чекбокса его имитацию - символ галочки из шрифта Marlett, который мы будем вставлять или убирать при щелчке мышью по ячейке:
Щелкните правой кнопкой мыши по ярлычку листа, где находится список, и выберите в контекстном меню Исходный текст (Source Code). Вы должны попасть в редактор Visual Basic - в модуль листа.
Скопируйте туда этот код:
'Ставим флажок, если был одиночный щелчок по ячейке
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
Application.EnableEvents = False
Target.Font.Name = "Marlett"
Target = "a"
Application.EnableEvents = True
End If
End Sub
'Снимаем флажок, если был двойной щелчок по ячейке
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
Application.EnableEvents = False
Cancel = True
Target.ClearContents
Application.EnableEvents = True
End If
End Sub
Первая половина кода проверяет, не было ли одиночного щелчка по ячейкам в диапазоне A2:A100 и если был - то ставит "галочку" - знак, который в шрифте Marlett находится на букве "а". Вторая половина этого кода снимает флажок при двойном щелчке по ячейке. При необходимости замените в этом коде "A2:A100" на Ваш диапазон.
При необходимости подсчитать количество помеченных элементов всегда можно использовать простую формулу, которая подсчитывает количество ячеек с буквой "а" в нашем диапазоне:
=СЧЁТЕСЛИ(A2:A100;"a")
=COUNTIF(A2:A100;"a")
Если надо, чтобы пользователь мог пометить только один элемент из списка, то код упрощается – достаточно всего одного макроса:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
Application.EnableEvents = False
Range("A2:A100").ClearContents
Target.Font.Name = "Marlett"
Target = "h"
Application.EnableEvents = True
End If
End Sub
Этот макрос сначала полностью очищает наш столбец А, а потом вводит в текущую ячейку букву "h", которая в шрифте Marlett даст нам характерный символ выбора по типу "один-из" – жирную точку. Два таких символа поставить, таким образом, не получится – доступен будет только выбор одного элемента из списка. Чтобы извлечь выбранный элемент, можно использовать стандартную функцию ВПР (VLOOKUP), которая будет искать символ точки (т.е. букву "h") в первом столбце нашей таблицы и, найдя, выдавать фамилию из второго:
Ссылки по теме
- Маркированные и нумерованные списки в Excel как в Word
- Что такое макрос? Куда вставлять код макроса на VBA? Как их использовать?
- Что такое функция ВПР (VLOOKUP), как ее использовать для подстановки значений
Range("A2:A10,A12:A100") - ячейка А11 исключается
Например:
если в ячейках столбце К есть данные, то в этих строках столбца L галки запрещены
Николай, а как возможно автоматически сортировать таблицу по столбцу с флажком, так чтобы они оказывались в конце списка? Просто пустые ячейки не сортируются, возможно туда стоит вставлять бесцветный символ? Буду признаетелен, если подскажите как это возможно сделать!
Пользуюсь данным кодом, все отлично, но появилась необходимость в некоторых дополнениях.
Подскажите пожалуйста, какое максимальное количество диапазонов можно применить в данном коде и есть ли такая возможность сделать так, чтобы в некоторых диапазонах можно было ставить только один флажок?
Можно ли сделать так, чтобы при внесении "крыжиков", в этой же строке, в "энной" ячейке, автоматически проставлялась дата (день, месяц, год) внесения "крыжика".
Подскажите, как сделать такой "крыжик" блокировкий?
Я имею ввиду если он ставится то все ячейки строки слева от него блокируются паролем и крыжик снять можно так же только с паролем.
Не сохраняется макрос, якобы во 2 строке ошибка - выделяется красным!
А как можно сделать появление галочки при двойном щелчке (чтоб исключить нечаянное проставление)?
Написал такой код, логически верно все
'Ставим флажок, если был двойной щелчок по ячейке Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("W2:NW100")) Is Nothing Then ....но выдает ошибку
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Range("H2:H1000")) Is Nothing Then If Target <> "" Then Application.EnableEvents = False Cancel = True Target.ClearContents Application.EnableEvents = True Else Application.EnableEvents = False Target.Font.Name = "Marlett" Target = "a" Application.EnableEvents = True Cancel = True End If End If End SubМожно вместо второй галочки другой символ.
Дублируем первый код с заменой адреса столбца?
Я права?
Y=cells(rows.count,1).end(xlUp).row
а потом переписать с этого листа на нужный вам, те строки (от 2 до Y), в которых есть галочка
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
на строку
if target.column=1 and target.row>1 then
В таком случае проверяется, если мы жмем в первом столбце, то только тогда ставить галочку.
If Target.Cells.Count > 1 Then
как этого избежать?, но при вставке в шаблон вариант
08.05.2015 16:31:10 - работает, но Worksheet_BeforeDoubleClick - не удобен.
Огромное спасибо за пример, очень помог в моем проекте:D
Т.к. у меня выдает ошибку run-time error '13' : type mismatch
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("tSites[Checked]")) Is Nothing Then 'имя таблицы[колонка]
If Target.Value = "" Then ' при первом щелчке ставим галочку, если ячейка пустая
Application.EnableEvents = False
Target.Font.Name = "Marlett"
Target = "a"
Application.EnableEvents = True
A = Target.Column
Else ' если ячейка не пустая, то очищаем ея
Application.EnableEvents = False
Target.Clear
Application.EnableEvents = True
End If
End If
Однако, если не уходить из ячейки, то второй щелчок её не очищает. Если же сменить ячейку и потом вернуться - то все работает.
Как сделать так,чтобы при первом щелчке - ставилась галочка, при втором - очищалась ?