Очень часто при работе с формами(UserForm) приходится заполнять значениями КомбоБоксы(ComboBox) и ТекстБоксы(TextBox). Иногда однотипными данными. Или очистить их все после выполнения какого-либо действия. А может проверить, все ли элементы заполнены, прежде чем продолжать действие. Если КомбоБоксов/ТекстБоксов пару штучек - не проблема

TextBox1 = ""
TextBox2 = ""

и всех делов. А если их порядка двадцати? Или больше? Мало того, что писать это все долго, так еще и код растягивается, как портянка. Но это можно сделать гораздо проще и удобнее. Воспользоваться можно несколькими способами.
Плюс ко всему, подобные действия может потребоваться производить не только с элементами на пользовательских формах(UserForm), но и с элементами на листе(ActiveX и элементами управления форм: вкладка Разработчик(Developer) -Вставить(Insert)). Разберем поочередно каждый из них и чуть больше:

  • Действия с контролами на форме (UserForm)
  • Действия с элементами ActiveX на листе
  • Действия с элементами управления форм на листе
  • Действия одновременно и с элементами ActiveX и с элементами управления форм на листе

  • Действия с контролами на форме (UserForm)
    Этот способ наиболее популярен, если необходимо произвести однотипные действия со всеми ТекстБоксами на форме:

    Sub All_TextBoxes()
        Dim oControl As Control
        For Each oControl In UserForm1.Controls
            If TypeOf oControl Is MSForms.TextBox Then
                oControl.Value = ""
            End If
        Next oControl
    End Sub

    В примере элементы формы очищаются, но в код можно подставить любое действие - добавление нового элемента списка(для ComboBox), поменять представление данных в TextBox, проверить наличие данных в проверяемом элементе и т.д. Тут уж зависит от поставленной задачи. Конечно, подобным способом можно проделать разные действия и с остальными элементами формы. Только надо будет заменить тип для проверки элементов:

    ComboBox - MSForms.ComboBox
    CheckBox - MSForms.CheckBox
    CommandButton - MSForms.CommandButton
    Frame - MSForms.Frame
    Image - MSForms.Image
    Label - MSForms.Label
    ListBox - MSForms.ListBox
    MultiPage - MSForms.MultiPage
    SpinButton - MSForms.SpinButton
    TabStrip - MSForms.TabStrip
    ToggleButton - MSForms.ToggleButton

    Это не единственный способ проделывания однотипных действий с элементами формы. Код ниже использует имена элементов для обращения к ним:

    Sub All_TextBoxes()
        Dim li As Long
        For li = 1 To 10
            UserForm1.Controls("TextBox" & li).Value = li
        Next li
    End Sub

    Недостаток данного метода: имена элементов должны строго соответствовать используемым в коде и лишь нумерация на конце имени должна различаться. За нумерацию отвечает переменная li и, конечно, цикл, в котором задается начальное и конечные значения. В примере ТекстБоксам с именами от "TextBox1" до "TextBox10" будут подставлены значения номеров самих ТекстБоксов. Но такой недостаток может быть очень полезным. Например, если необходимо проделать действия не над всеми ТекстБоксами, а лишь над некоторыми из них. Тогда все, что необходимо дать ТекстБоксам определенные имена с нумерацией("ToDB1","ToDB2" и т.д.) и в зависимости от имени можно проделывать различные действия: стирать значения, менять свойства элементов и т.д.
    И есть еще один плюс такого подхода: когда необходимо заполнить значения ТекстБоксов значениями ячеек. Скажем надо заполнить 10 ТекстБоксов(с именами TextBox1, TextBox2, TextBox3 и т.д.) из ячеек диапазона A2:A11 листа с именем "Лист2"(т.е. из 10 ячеек, начиная с ячейки A2). Код будет выглядеть так:

    Sub Fill_TextBoxes_FromCells()
        Dim li As Long
        For li = 1 To 10
            UserForm1.Controls("TextBox" & li).Value = Sheets("Лист2").Range("A" & li).Value
            'или применить Cells вместо Range
            'UserForm1.Controls("TextBox" & li).Value = Sheets("Лист2").Cells(li, 1).Value
        Next li
    End Sub

    Подробнее про обращение к диапазонам из VBA можно узнать из этой статьи: Как обратиться к диапазону из VBA

    Скачать пример

      Tips_Macro_WorkWithGroupControls.xls (51,5 КиБ, 4 180 скачиваний)

    P.S. Небольшое дополнение: нужные элементы можно просто помещать внутрь объекта Frame. Тогда можно будет применять цикл исключительно по элементам внутри этого Frame(предположим, что Frame называется Frame1):

    Sub All_TextBoxes_InFrame()
        Dim oControl As Control
        For Each oControl In Frame1.Controls
            If TypeOf oControl Is MSForms.TextBox Then
                oControl.Value = ""
            End If
        Next oControl
    End Sub


    Действия с элементами ActiveX на листе
    Кстати говоря, примерно так же можно перебрать элементы ActiveX не на форме, а на листе(вставляются на лист через вкладку Разработчик(Developer) -Вставить(Insert) -Элементы ActiveX(ActiveX Controls)). Например, снимем флажки со всех CheckBox-ов:

    Sub Off_ActiveXCheckBoxes()
        Dim oControl
        'цикл по всем объектам на листе
        For Each oControl In ActiveSheet.DrawingObjects
            'определяем тип объекта - должен быть OLEObject(так определяется ActiveX)
            If TypeName(oControl) = "OLEObject" Then
                'необходимо дополнительно проверить тип элемента
                If TypeOf oControl.Object Is MSForms.CheckBox Then
                    oControl.Object.Value = 0
                End If
            End If
        Next oControl
    End Sub

    Для этих элементов на листе в строке TypeOf oControl.Object Is MSForms.CheckBox для проверки типа(MSForms.CheckBox) используются те же значения, что и для контролов на форме. Перечень приведен выше.


    Действия с элементами управления форм на листе
    Но помимо ActiveX(к слову устаревших и не рекомендованных к использованию) на листе могут быть и другие, более новые флажки - элементы форм(вставляются на лист через вкладку Разработчик(Developer) -Вставить(Insert) -Элементы управления формы(Form Controls)). К ним подход уже другой:

    Sub Off_ShapeCheckBoxes()
        Dim oControl
        'цикл по всем объектам на листе
        For Each oControl In ActiveSheet.DrawingObjects
            'определяем тип объекта - если это Элемент форм, то будет указание на конкретный тип
            If TypeName(oControl) = "CheckBox" Then
                oControl.Value = 0
            End If
        Next oControl
    End Sub

    В данном случае для определения конкретного типа контрола используется TypeName. В коде выше нам нужны CheckBox-ы и на них и проверяем. А вот список основных элементов форм, которые доступны по умолчанию для всех версий для вставки на лист:
    Button - кнопка
    DropDown - поле со списком
    CheckBox - флажок
    Spinner - счетчик
    ListBox - список
    OptionButton - радиокнопка
    GroupBox - группа(рамка)
    Label - надпись
    ScrollBar - полоса прокрутки


    Действия одновременно и с элементами ActiveX и с элементами управления форм на листе
    Если совместить, то можно снять флажки со всех checkBox-ов - и ActiveX и Элементов форм:

    Sub Off_AllCheckBoxes()
        Dim oControl
        'цикл по всем объектам на листе
        For Each oControl In ActiveSheet.DrawingObjects
            'определяем тип флажка: ActiveX или Элемент форм
            Select Case TypeName(oControl)
            Case "OLEObject"    'ActiveX - необходимо дополнительно проверить тип элемента
                If TypeOf oControl.Object Is MSForms.CheckBox Then
                    oControl.Object.Value = 0
                End If
            Case "CheckBox"     'Элемент форм CheckBox
                oControl.Value = 0
            End Select
        Next oControl
    End Sub

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

    Loading

    14 комментариев

    1. Вы правильно идете. Просес овладения VBA (как и любой) содержит две параллельные дороги - теория и задачи. По теории горы книг и все переписывают азы друг у друга, да ктому же читаешь на 30 странице и не помнишь что на 1 -ой. Решение отдельной задачи с подробными коментариями, это вторая, очень нужная Дорога. Спосибо.

    2. А как сделать тоже самое, имею ввиду очистку, только на листе.Сейчас пользуюсь
      таким кодом.
      Private Sub test()
      TextBox1 = ""
      TextBox2 = ""
      TextBox3 = ""
      TextBox4 = ""
      TextBox5 = ""
      End Sub

    3. Спасибо пришлось самой найти
      For iCount = 1 To 10
      OLEObjects("TextBox" & iCount).Object.Value = ""
      Но я вам благодарна, что ваши идеи заставляют извилины работать.

    4. При использовании combobox (в макросе Exel) часто возникают новые значения,которые должны быть занесены в ниспадающий список. Мохно ли автоматизировать занесение этих значений в список после набора их в окне combobox.

    5. Добрый день у меня на форме 10 комбобоксов я использую такой код т.е. пишу для каждого соmbobox отдельно

      With Sheets("справка")
      LastRow = .Cells(Rows.Count, 2).End(xlUp).Row
      Me.ComboBox1.List = .Range("b2:b" & LastRow).Value
      Me.ComboBox2.List = .Range("b2:b" & LastRow).Value
      Me.ComboBox3.List = .Range("b2:b" & LastRow).Value

      End With

      Как можно сократить код.Спасибо.

    6. Ваш сайт для меня - просто находка! Какой вопрос ни возникает, здесь есть практически все ответы. Спасибо! И за эту статью отдельное спасибо!

      1. На форме используются текстбоксы:
        txtb_НачальнаяДата,txtb_КонечнаяДата,txtb_Cтоимость и т.д. до 10 штук. Начальные буквы txtb, а далее меняются окончания. Как очистить текстбоксы.
        Вариант :txtb_НачальнаяДата.Value="" -это понятно ,а через цикл можно?

        1. Юрий, применить смекалку и доступные инструкции проверки:

          Sub All_TextBoxes()
              Dim oControl As Control
              For Each oControl In UserForm1.Controls
                  If TypeOf oControl Is MSForms.TextBox Then
                      If left(Control.Name,4) = "txtb" Then
                          oControl.Value = ""
                      End If
                  End If
              Next oControl
          End Sub
    7. Подскажите пожалуйста, как мне заполнить Textbox, которые имеют разные названия (по типу "a", "h"...) из ячеек с7:с12.
      Сейчас я использую такой код, но он очень уж не оптимальный исходя из того что мне надо заполнять 10 заданий, 10 вариантов, в каждом по 7 textbox (700 строчек).
      ...
      If Co = "Вариант1" Then
      h = Range("c7")
      d = Range("c8")
      q = Range("c9")
      h1 = Range("c10")
      d1 = Range("c11")
      q1 = Range("c12")
      ElseIf Co = "Вариант2" Then
      h = Range("d7")
      d = Range("d8")
      q = Range("d9")
      h1 = Range("d10")
      d1 = Range("d11")
      q1 = Range("d12")
      ElseIf Co = "Вариант3" Then
      h = Range("e7")
      d = Range("e8")
      q = Range("e9")
      h1 = Range("e10")
      d1 = Range("e11")
      q1 = Range("e12")
      ...
      заранее спасибо.

      1. Tert, смысла нет использовать устаревший тип integer в текущих реалиях(пара байт в памяти погоды сейчас не сделает, а ограничения по макс.значению у Integer сильно отличаются от Long). Поэтому скажем так - привычка не использовать менее удобных тип и все. Хотя ничего страшного тоже не случится, если применить в статье Integer - в конкретных приведенных кодах без разницы какой тип применить.

    Добавить комментарий

    Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.