Главная » Макросы для Excel » Скрытие отмеченных строк (с использованием элементов управления Checkbox, или без них)

Скрытие отмеченных строк (с использованием элементов управления Checkbox, или без них)
Использование элементов управления Checkbox для скрытия и отображения строк

В прикреплённом файле - 3 варианта реализации:

  • с использованием CheckBox с панели инструментов "Элементы управления"
  • с использованием CheckBox с панели инструментов "Формы"
  • без использования Checkbox - галочки (отметки) ставятся макросом
    (с использованием шрифта Marlett)

 

Для каждого из вариантов использованы свои макросы, скрывающие и отображающие строки
(в первых двух вариантах требуется ещё и скрывать сами чекбоксы)

В третьем варианте для скрытия строк применён автофильтр.

Вложения:
Checkboxes.xls61.5 КБ
  • 34808 просмотров

Комментарии

Настройки просмотра комментариев

Выберите нужный метод показа комментариев и нажмите "Сохранить установки".
Гость, 4 Май 2024 - 20:51.#1

день добрый, использовал ваш третий вариант использования chekbox...мне нужно скопировать лист на котором находится этот макрос, но после копирования все перестает работать, макрос вроде копируется но скрытие больше не работает

Гость, 11 Июл 2018 - 15:36.#2

Добрый день!

Подскажите, пожалуйста, возможно ли в LinkedCell вместо "ИСТИНА" написать "1" (когда флажок установлен), вместо "ЛОЖЬ" - "2" (флажок снят)?

Игорь (администратор сайта), 30 Авг 2017 - 20:23.#3

Здравствуйте, Виктор.
Можно ускорить, если скрывать сразу все строки (одновременно)
Пример кода можно посмотреть здесь:
http://excelvba.ru/code/ConditionalRowsDeleting
возможно, надо будет переделать с проверки значений ячеек на проверку чекбоксов

Виктор, 30 Авг 2017 - 14:59.#4

Здравствуйте! Пробовал разобраться во втором варианте (вкладка "нормальные чекбоксы"). В случае когда большое количество строк с чекбоксами (допустим 150 строк, а не 5 как в примере), макрос для скрытия выполняется очень долго. Тажке, скорость его выполнения почему-то зависит от наличия значений на других листах (чем больше значений на других листах, тем он медленнее). Что можно сделать, чтобы скорость макроса была выше при большом количестве скрываемых строк?

Игорь (администратор сайта), 14 Июн 2017 - 20:49.#5

Ну так понятно, что должны быть в книге эти объекты, - и галочка с именем CheckBox1, и подключение с именем "http://excelvba.ru"
А если их нет, - конечно ошибку будет макрос выдавать
Если сами не разберетесь, - можем сделать под заказ

Михал Иваныч, 14 Июн 2017 - 20:38.#6

Игорь так же Ошибка "Run-time error 424 object required на строке:

CheckBox1.Enabled = False: ActiveWorkbook.Connections("http://excelvba.ru").Refresh

Может необходимо создание объекта на запрос? ,но я могу быть ошибаться.

Игорь (администратор сайта), 14 Июн 2017 - 20:11.#7

Как-то так можно сделать:

Sub CheckBox()
    Select Case True
 
        Case CheckBox1.Value
            CheckBox1.Enabled = False: ActiveWorkbook.Connections("http://excelvba.ru").Refresh
 
        Case CheckBox2.Value
            CheckBox2.Enabled = False: ActiveWorkbook.Connections("http://abvlecxe.ru").Refresh
 
        Case CheckBox3.Value
            MsgBox "включена третья галочка, а первые две - нет"
 
        Case Else
            MsgBox "Ни одной галочки не включено"
 
    End Select
End Sub

Михал Иваныч, 14 Июн 2017 - 19:27.#8

Здравствуйте, Игорь.

Помогите пожалуйста с кодом, выдает ошибку 424 ,((
Задача при проставленной галочке в чекбоксе макрос дает запрос, если нет галочки переходим на следующий запрос.

Sub CheckBox()
If CheckBox1.Value = True Then
ActiveWorkbook.Connections("http://excelvba.ru").Refresh
CheckBox1.Enabled = False
End If
End Sub
Sub CheckBox()
If CheckBox2.Value = True Then
ActiveWorkbook.Connections("http://abvlecxe.ru").Refresh
CheckBox1.Enabled = False
End If
End Sub

Андрей, 18 Янв 2017 - 13:50.#9

Если бы так просто. Это поле неактивно (серое) в свойствах чекбокса. Поэтому при сортировке перемещаются только ячейки, чекбоксы на месте стоят.
Как его "пометить", желательно программно - хз.
Обходной вариант - перерисовывать чекбоксы после сортировки, но как отловить эту самую сортировку...

Игорь (администратор сайта), 17 Янв 2017 - 16:29.#10

Здравствуйте, Андрей
Выделите все чекбоксы, и в свойствах поменяйте режим привязки к ячейкам (опция «перемещать и изменять вместе с ячейками»)

Андрей, 17 Янв 2017 - 12:47.#11

Подскажите пожалуйста, как привязать чекбокс к ячейке? Проблема в следующем: на листе есть автофильтр один из столбцов которого реализован в виде чекбоксов. все прекрасно работает при фильтрации строк, но если применить сортировку то привязка чекбокса "слетает". Например: Автофильтр 50 строк столбец "А" - чекбоксы связанные (.LinkedCell="$A$2) с ячейками в которые вписаны. После применения сортировки в автофильтре чекбоксы становятся связаны с произвольной ячейкой столбца "А". (чекбокс изначально нарисованный и связанный с ячейкой "А2" после сортировки становится связан с ячейкой "А9", но рисуется по прежнему в "А2")

Игорь (администратор сайта), 10 Окт 2013 - 08:16.#12

Юля, а какое отношение ваш вопрос имеет к теме статьи?

Макрос для вашего случая (делает выделенные ячейки полужирными)

Selection.font.bold = TRUE

Юля, 10 Окт 2013 - 07:36.#13

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

Игорь (администратор сайта), 17 Окт 2012 - 01:02.#14

Сергей, я же не телепат, чтобы, не видя вашего файла, подсказать, в чем там проблема, и что написать надо...

В моём же примере всё работает - возьмите код оттуда.
И обратите внимание - чекбоксы (галочки) бывают двух видов, используйте тот код, который идёт для используемых вами чекбоксов.

Если сами не разберетесь - оформляйте заказ, прикрепляйте файл, объясняйте, как что должно работать, - тогда сам все сделаю (не бесплатно)

Сергей, 17 Окт 2012 - 00:28.#15

Доброй ночи!
смотрю и никак не пойму с чекбоксами. имеется ряд столбцов (согласно вашего примера делаем две кнопки - скрыть и отобразить) с яцейки столбцов ставим чек боксы. далее не пойму что и где писать что б работала кнопка спрятать те что без галочки. Помогите пожалуйста если не затруднит (офис 2007 винда 7/64)
С уважением, Сергей!

Nika.Dn, 30 Сен 2012 - 17:15.#16

Вообще-то данные из Access у меня загружаются именно при активации листа, а потом таблицы динамически обновляются и изменяются при переключении по гипперссылкам (своего рода меню такое), при этом один и тот же лист может быть активным, поэтому галочки проставляются как при активации листа, так и при прохождении по пунктам моего меню. Судя по всему ОДИН раз галочки проставить не получится

Игорь (администратор сайта), 29 Сен 2012 - 12:35.#17

Код можно писать куда угодно - в любой модуль. Вызывать его можно тоже из любой процедуры.

Вот вариант кода, который не будет удалять элементы ActiveX, и не будет ошибочно ставить галочки в ячейку a1

Sub ПреобразоватьЛогическиеЗначенияВГалочки()
    On Error Resume Next: Application.ScreenUpdating = False
    Dim sh As Worksheet, cell As Range, ra As Range, sha As Shape
    Const chsize = 15    ' размеры чекбокса

    ' перебираем все листы
    For Each sh In ActiveWorkbook.Worksheets
 
        For Each sha In sh.Shapes    ' перебираем все графические объекты
            ' удаляем элементы управления из набора «Формы»
            If sha.Type = msoFormControl Then sha.Delete
        Next sha
 
        ' все «логические» ячейки на листе
        Set ra = Nothing: Set ra = sh.UsedRange.SpecialCells(xlCellTypeConstants, xlLogical)
        If Not ra Is Nothing Then
            ' перебираем все «логические» ячейки
            For Each cell In ra.Cells
                ' делаем текст ячейки невидимым
                cell.Font.Color = vbWhite
 
                ' вычисляем координаты для вставки чекбокса
                x = cell.Left + cell.Width / 2 - chsize / 2
                y = cell.Top + cell.Height / 2 - chsize / 2
 
                ' вставляем чекбокс в центр ячейки
                With sh.CheckBoxes.Add(x, y, chsize, chsize)
                    .Value = CBool(cell)
                    .LinkedCell = cell.Address
                    .Display3DShading = False
                    .Caption = ""
                End With
            Next cell
        End If
    Next sh
 
    Application.ScreenUpdating = True    ' тот редкий случай, когда эта строка необходима
End Sub

PS: Зачем каждый раз при активации листа заново проставлять все галочки?
ОДИН РАЗ запустите код (после импорта данных из access) - и больше не надо.

Nika.Dn, 29 Сен 2012 - 05:01.#18

Простите, возникли вопросы, куда писать этот код? Где вызывать процедуры? Почему у меня в книге удалились элементы ActiveX (в основном Data Picker 6.0)? Почему чекбокс стоит в ячейке A1 на всех листах?
P.S. Код сначала вставила в модуль книги, потом перенесла его в обычный, который отвечает за интерфейс и вызвала процедуру в WorkSheet_Activаte

Nika.Dn, 29 Сен 2012 - 04:16.#19

Спасибо Вам, Игорь, огромное. Смотрю на код и понимаю, что сама бы я до такого не додумалась. Вернее гипотетически я понимаю что нужно сделать, а как на VBA записать еще толком не знаю. Еще раз СПАСИБО!

Игорь (администратор сайта), 29 Сен 2012 - 02:43.#20

Сделал я вам макрос для замены логических значений на галочки,
без изменения значений в ячейках.
Работает для всех листов активной книги.

Проверяйте:

Sub ПреобразоватьЛогическиеЗначенияВГалочки()
    On Error Resume Next: Application.ScreenUpdating = False
    Dim sh As Worksheet, cell As Range
 
    ' перебираем все листы
    For Each sh In ActiveWorkbook.Worksheets
        Const chsize = 15
        sh.DrawingObjects.Delete
 
        ' перебираем все «логические» ячейки
        For Each cell In sh.UsedRange.SpecialCells(xlCellTypeConstants, xlLogical).Cells
 
            ' делаем текст ячейки невидимым (белым)
            cell.Font.Color = vbWhite
 
            ' вычисляем координаты для вставки чекбокса
            x = cell.Left + cell.Width / 2 - chsize / 2
            y = cell.Top + cell.Height / 2 - chsize / 2
 
            ' вставляем чекбокс в центр ячейки
            With sh.CheckBoxes.Add(x, y, chsize, chsize)
                .Value = CBool(cell)
                .LinkedCell = cell.Address
                .Display3DShading = False
                .Caption = ""
            End With
 
        Next cell
    Next sh
    Application.ScreenUpdating = True ' тот редкий случай, когда эта строка необходима
End Sub

Sub УдалитьВсеГрафическиеОбъекты()
    On Error Resume Next: On Error Resume Next
    For Each sh In ActiveWorkbook.Worksheets    ' перебираем все листы
        sh.DrawingObjects.Delete    ' удаляем чекбоксы
    Next sh
End Sub

Результат (слева - что было, справа - что стало)

Вставка чекбоксов (галочек) вместо логических значений в Excel

Nika.Dn, 29 Сен 2012 - 02:12.#21

Да для простого отображения, не оч. красиво смотрится ИСТИНА или ЛОЖЬ в ячейках, гораздо красивее галочки (а вот какие лучше использовать, подскажите пожалуйста Вы: мне больше понравились те, которые шрифтом делаются, но так, Вы говорите, нужно менять значение ячеек). Так как таблица заполняется данными из БД Access, то ничего естественно менять в данных не нужно, просто показать, причем макрос (т.е. процедура) наверное должен быть глобальным, так как таблицы будут все из БД и в некоторых из них будут логические поля(столбцы). Вообщем при активации листа, после выполнения

LO.DataBodyRange.CopyFromRecordset Recordset

где LO - объект "умная таблица"
просматриваем таблицу на наличие логических значений и там где ИСТИНА, отображаем галочку, а где ЛОЖЬ - пустая ячейка соответственно, при этом значения в ячейке не меняем, т.к. в БД должны сохраниться логические значения. Но тогда придется с ячейками связывать чекбоксы, а это будет по-любому притормаживать.(Не хотелось бы). Данные будут редактироваться либо формой, либо (что скорее всего) элементами ActiveX над самой таблицей, строки будут изменяться, удаляться или добавляться в таблицу динамически, и только после этого сохраняться обратно в БД.

Игорь (администратор сайта), 28 Сен 2012 - 12:03.#22

А какие «галочки» вас интересуют?
Который выполнены при помощи чекбоксов, или буквой «a» шрифтом Marlett?
Сделать-то можно, но это будет макрос, заменяющий в таблице одни значения другими
Обратную операцию (возврат логических значений в ячейки) тоже надо будет делать, при активации любого другого листа?

Создаваемые «галочки» должны менять значение ячеек?
Можно написать макрос, который всем логическим ячейкам назначит белый цвет шрифта, и поверх каждой такой ячейки нарисует чекбокс, связанный с этой ячейкой.
Но - при большом объёме данных это будет заметно тормозить (тысячи графических объектов на листе заметно снизят скорость работы с данными)

Для чего это надо сделать? (для удобства работы, для вывода на печать, ещё что-то?)
Может, есть какой-то более простой способ...

Nika.Dn, 28 Сен 2012 - 11:02.#23

Здравствуйте, Игорь!
У меня такой вопрос, а если необходимо отображать галочки не по щелчку мышки, а сразу при активации листа, как это сделать программно?
То есть имеем ListObject, и в ней определенные столбцы логического типа. При создании и отображении в ячейках пишутся значения true и false соответственно. Можно ли сделать так, чтобы отображались ваши "галочки"? Грубо говоря, нужно обойти все ячейки в таблице (независимо от количества строк и столбцов) и превратить значения true в "галочки"

Игорь (администратор сайта), 18 Июн 2012 - 23:14.#24

Здравствуйте, Елена.
Какую ещё мою процедуру, в какой исходный код, зачем?
Я лишь показал вам пример кода VBA для работы с «умной таблицей» (списком Excel 2003).
Этот код не создаёт таблицу, а работает с ней.

Вам на форум по Excel лучше обратиться, - там вам подскажут (я с СУБД пока не дружу).
Да и вопрос ваш не имеет никакого отношения к теме статьи...

nika.dn, 18 Июн 2012 - 22:21.#25

А разве она не соэдается динамически? Если нет то как ее создать на указанном или активном листе? Я так поняла, что Вашу процедуру нужно записать в исходном коде нужного мне листа и вызвать ее в Worksheeet_Activate. Мне собственно нужночтобы при выборе листа создавалась таблица с данными из базы данных Access по конкретному запросу. Поскольку аналогичным образом у меня будут открываться почти все листы (динамически и программным кодом VBA создаваться таблицы, элементы управления: текстовые поля, выпадающие списки и др.), то малюсенький пример использования мне бы помог. Если сможете, то помогите, пожалуйста. Связь с БД я использую через ADO (это потому что возможно буду переходить на SQL Server) И еще вопрос по существу, посоветуйте, пожалуйста, как профессионал, какую СУБД лучше использовать вместе с Excel и с помощью чего к ней подключаться. Я много в инете об этом читала, но ответов еа свой вопрос так и нре рнашла, так как в этом вопросе я не специалист. Я понимаю, что все зависит от конкретной задачи. У меня обычная реляционная БД с претензией на объектно-реляционную БД (отсюда и возможный переход на SQL Server)ю Задача - учет торговли и склада (остатки, движение товара и денег) - специфический, поэтому и не использую готовые программы (такие как 1С и т.п.), а пишу сама. Выбор пал на Excel, так как и Вы думаю, что нерешаемых задач в Excele нет (если конечно знать VBA, хотя бы % на 20 от Вашего). Не посчитайте за наглость или за грубую лесть мои высказывания. С уважением, Елена

Игорь (администратор сайта), 18 Июн 2012 - 17:44.#26

Если строка Set LO = ActiveSheet.ListObjects(1)
выдаёт такую ошибку, значит, на активном листе Excel нет ни одной «умной таблицы».

nika.dn, 18 Июн 2012 - 16:31.#27

Здравствуйте, Игорь.
Похоже, что "умная таблица" оказалась умнее меня :(. Попробовала поюзать ваш код-пример. В строке
Set LO = ActiveSheet.ListObjects(1)
пишет ошибку run-time error '9' Subscript out of range
Что не так?
У меня Office 2010 Windows7

nika.dn, 24 Май 2012 - 18:26.#28

Ой, спасибо, Вам, Игорь, большое за быстрый ответ. Дальше я с этой таблицей сама разбираться буду. Ух, я ее... А то в последнее время только она меня... (Шутка)
Извините за оффтоп и еще раз огромное спасибо

Игорь (администратор сайта), 24 Май 2012 - 13:18.#29

Вот вам пример кода для работы с «умной таблицей»:
(в Excel 2003 эта умная таблица носила название «список»)

Sub test()
    Dim LO As ListObject    ' «умная таблица»
    ' указываем имя или номер умной таблицы (списка)
    Set LO = ActiveSheet.ListObjects(1)
 
    ' красим в красный цвет диапазон с данными
    LO.DataBodyRange.Interior.Color = vbRed
    ' красим в зеленый цвет заголовок умной таблицы
    LO.HeaderRowRange.Interior.Color = vbGreen
 
    ' заполняем первый столбец умной таблицы (списка) данными
    Dim cell As Range
    For Each cell In LO.DataBodyRange.Columns(1).Cells
        cell = "Ячейка " & cell.Address
    Next cell
 
 
    ' заполняем второй столбец таблицы «галочками»
    LO.DataBodyRange.Columns(2).Value = "a"
    LO.DataBodyRange.Columns(2).Font.Name = "Marlett"
End Sub

Подробнее - во встроенной справке по объекту ListObject

nika.dn, 24 Май 2012 - 11:51.#30

Игорь, огромное Вам СПАСИБО за помощь. Дело в том, что я долгое время не могла решить проблему с CheckBox-ами в ячейках. Сама пробовала, инет перешерстила вдоль и поперек, даже на киберфоруме помощи просила. Ответ один - вставить чекбоксы в ячейку нельзя. А если и можно, то там к координате top привязка. А у меня "умная" или не очень :) динамическая таблица, которая заполняеся из БД Access динамически и мне нужно было выделять определенные строки и обрабатывать их прямо в коде. Вставлять в ячейку контролы и привязывать их руками нереально (так как нужно это в зависимости от количества строк в таблице БД динамически, а это только кодом) Вообщем хочу поблагодарить вас за идею ставить галочки в ячейке шрифтом - идеальный вариант для динамических таблиц. Только вот подсказали бы Вы мне, начинающей в области VBA, как работать с "умной таблицей" как с объектом VBA в коде, то бишь программно. Я просто не могу найти нигде информации: ищу то ли плохо, то ли все не там.

Вячеслав, 17 Авг 2011 - 19:01.#31

Спасибо, попробуем прикрутить

Игорь (администратор сайта), 17 Авг 2011 - 18:55.#32

Да тут нужен гораздо более простой макрос.
Вчера такой делал - код скрывает столбце при двойном щелчке на ячейке:
programmersforum.ru/showpost.php?p=860687&postcount=2

Вячеслав, 17 Авг 2011 - 18:51.#33

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

Игорь (администратор сайта), 17 Авг 2011 - 18:41.#34

Смотря какой из трех вариантов вас интересует...
Автофильтр, к примеру, для скрытия столбцов не подойдёт.

Надо переделывать код - а что именно вам надо, я не знаю.
Обратитесь на форумы по Excel, прикрепите пример своего файла, - и вам обязательно помогут изменить код под ваши нужды.

Вячеслав, 17 Авг 2011 - 18:38.#35

Отличная вещь. А подскажите как сделать чтобы вместо строк скрывались столбцы?

Гость, 5 Авг 2011 - 10:54.#36

Благодарю, все заработало!

Игорь (администратор сайта), 3 Авг 2011 - 18:18.#37

Просто измените в макросе критерий автофильтра - тогда макрос будет скрывать отмеченные строки:

Sub СкрытьОтмеченные3()
    On Error Resume Next
    [a1:c1000].AutoFilter Field:=2, Criteria1:=""
End Sub

Гость, 3 Авг 2011 - 15:33.#38

Подскажите пожалуйста, как в третьем варианте сделать скрытие ОТМЕЧЕННЫХ строк, а не скрытие неотмеченных.

Антон, 29 Июл 2011 - 00:51.#39

Понял, искренне вам благодарен за оказанную помощь.

Игорь (администратор сайта), 28 Июл 2011 - 19:19.#40

Для "умных таблиц" надо немножно изменить код - применять автофильтр не к листу, а к таблице:

Пример для третьего варианта, который без использования Checkbox (галочки ставятся макросом с использованием шрифта Marlett)

Sub ОтобразитьВсе3()
    ActiveSheet.ListObjects(1).Range.AutoFilter
End Sub
 
Sub СкрытьНеотмеченные3()
    On Error Resume Next
    ActiveSheet.ListObjects(1).Range.AutoFilter Field:=2, Criteria1:="<>"
End Sub

Антон, 28 Июл 2011 - 18:53.#41

Благодарю вас за макрос, Игорь. Отличное решение! В Excel 2007, по всей видимости, этот макрос не работает в "умных" таблицах. Есть ли какое-то решение? Хочется оставить и "умную" таблицу и пользоваться этими замечательными кнопками.

Гость, 20 Июл 2011 - 15:24.#42

Спасибо большое, чувствую что все просто должно быть, но сам допереть не смог )))

За макрос спасибо, просто, гениально и работает.

Игорь (администратор сайта), 20 Июл 2011 - 13:35.#43

В третьем варианте "галочка" и есть ни что иное, как самая обычная буква a, только набранная шрифтом Marlett.

Смените шрифт в столбце - и галочки превратятся в буквы.

Гость, 20 Июл 2011 - 13:29.#44

Касательно третьего варианта, поясните пож-та каким образом "а" заменяется на галочку?

Отправить комментарий

Содержание этого поля является приватным и не предназначено к показу.
CAPTCHA
Подтвердите, пожалуйста, что вы - человек:
             ____                            _   _       _ 
 __      __ |  _ \   _ __ ___    _ __ ___   | | | |     | |
 \ \ /\ / / | |_) | | '_ ` _ \  | '_ ` _ \  | | | |  _  | |
  \ V  V /  |  _ <  | | | | | | | | | | | | | |_| | | |_| |
   \_/\_/   |_| \_\ |_| |_| |_| |_| |_| |_|  \___/   \___/
Введите код, изображенный в стиле ASCII-арт.

Не получается применить макрос? Не удаётся изменить код под свои нужды?

Оформите заказ у нас на сайте, не забыв прикрепить примеры файлов, и описать, что и как должно работать.