Vba excel combobox сортировка

Обновлено: 05.01.2025

Пользователь может ввести новое значение (в случае текстового поля TextBox) или выбрать существующее значение (в случае списка ListBox).

Если comboBox привязан к источнику данных, comboBox вставляет в этот источник данных значение, выбранное пользователем. Если многослойное комбо-поле связано, свойство BoundColumn определяет, какое значение хранится в связанном источнике данных.

Список в ComboBox состоит из строк данных. Каждая строка состоит из одного или нескольких столбцов с заголовками (или без них). Некоторые приложения не поддерживают заголовки столбцов, другие — поддерживают только частично.

Свойство comboBox по умолчанию — это свойство Value.

Если вы хотите, чтобы в списке всегда появлялись несколько строк, может потребоваться использовать ListBox вместо ComboBox. Если вы хотите использовать ComboBox и ограничить значения для тех, кто находится в списке, можно установить свойство Style comboBox, чтобы управление выглядело как окно списка с отсевом.

События

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

Методы

Имя	Описание
AddItem	Для одного столбца ComboBoxметод AddItem добавляет элемент в список. Для многоуровневого ComboBox этот метод добавляет строку в список.
Clear	Удаляет все записи в списке в ComboBox.
Copy	Копирует содержимое объекта в буфер обмена.
Cut	Удаляет выбранную информацию из объекта и переносит ее в буфер обмена.
DropDown	Отображает список элемента управления ComboBox.
Paste	Передает содержимое буфера обмена объекту.
RemoveItem	Удаляет строку из списка в ComboBox.

Свойства

Имя	Описание
AutoSize	Возвращает или задает boolean, который указывает, автоматически ли объект обновляется для отображения всего содержимого. Для чтения и записи.
AutoTab	Возвращает или задает boolean, который указывает, происходит ли автоматическая вкладка, когда пользователь вводит максимально допустимое количество символов в текстовую часть коробки comboBox. Для чтения и записи.
AutoWordSelect	Возвращает или задает boolean, который указывает, является ли базовым элементом, используемым для расширения выбора, слово или один символ. Для чтения и записи.
BackColor	Возвращает или задает длинный, который указывает фоновый цвет объекта. Для чтения и записи.
BackStyle	Возвращает или задает набор integer, который задает фоновый стиль объекта. Для чтения и записи.
BorderColor	Возвращает или задает long, который указывает пограничный цвет объекта. Для чтения и записи.
BorderStyle	Возвращает или задает набор integer, который указывает тип границы управления. Для чтения и записи.
BoundColumn	Возвращает или задает вариант, определяя источник данных в многокомнатном ComboBox. Для чтения и записи.
CanPaste	Возвращает boolean, который указывает, содержит ли буфер обмена данными, поддерживаемые объектом. Только для чтения.
Column	Возвращает или задает Вариант, представляющие одно значение, столбец значений или двухмерный массив для загрузки в ComboBox. Для чтения и записи.
ColumnCount	Возвращает или задает long, представляю для отображения числа столбцов в комбо-окне. Для чтения и записи.
ColumnHeads	Возвращает или задает boolean, который указывает, отображается ли одна строка заголовков столбцов. Для чтения и записи.
ColumnWidths	Возвращает или задает строку, которая указывает ширину каждого столбца в многокомнатном ComboBox. Для чтения и записи.
CurTargetX	Возвращает long, представляющие предпочтительное горизонтальное положение точки вставки в многолинейной ComboBox. Только для чтения.
CurX	Возвращает или задает long, представляющие текущее горизонтальное положение точки вставки в многолинейной ComboBox. Для чтения и записи.
DragBehavior	Возвращает или задает набор integer, который указывает, включает ли система функцию перетаскивания для управления. Для чтения и записи.
DropButtonStyle	Возвращает или задает значение fmDropButtonStyle, которое представляет символ, отображаемый на кнопке сброса в ComboBox. Для чтения и записи.
Enabled	Возвращает или задает boolean, который указывает, может ли управление получать фокус и реагировать на события, созданные пользователем. Для чтения и записи.
EnterFieldBehavior	Возвращает или задает набор integer, который указывает поведение выбора при входе в ComboBox. Для чтения и записи.
ForeColor	Возвращает или задает длинный, который указывает цвет переднего плана объекта. Для чтения и записи.
HideSelection	Возвращает или задает boolean, который указывает, остается ли выбранный текст выделен, если у управления нет фокуса. Для чтения и записи.
IMEMode	Возвращает или задает integer, который указывает режим времени работы по умолчанию редактора метода ввода (IME) для управления. Для чтения и записи.
LineCount	Возвращает long, указывав количество строк текста в ComboBox. Только для чтения.
Список	Возвращает или задает вариант, представляющие указанную запись в ComboBox. Для чтения и записи.
ListCount	Возвращает длинный, который представляет количество записей списка в области управления. Только для чтения.
ListIndex	Возвращает или задает вариант, представляющие выбранный в настоящее время элемент в ComboBox. Для чтения и записи.
ListRows	Возвращает или задает длинный, который указывает максимальное количество строк, отображаемых в списке. Для чтения и записи.
ListStyle	Возвращает или задает набор integer, который указывает визуальное появление списка в ComboBox. Для чтения и записи.
ListWidth	Возвращает или задает вариант, который указывает ширину списка в ComboBox. Для чтения и записи.
Locked	Возвращает или задает boolean, который указывает, можно ли изменить управление. Для чтения и записи.
MatchEntry	Возвращает или задает набор integer, который указывает, как ComboBox выполняет поиск своего списка в качестве типов пользователей. Для чтения и записи.
MatchFound	Возвращает значение Boolean, которое указывает, соответствует ли тексту, который пользователь впечатал в ComboBox, какие-либо записи в списке. Только для чтения.
MatchRequired	Возвращает или задает значение Boolean, которое указывает, должно ли значение, внося в текстовую часть ComboBox, соответствовать записи в существующей части списка управления. Для чтения и записи.
MaxLength	Возвращает или задает длинный, который указывает максимальное количество символов, которые пользователь может ввести в ComboBox. Для чтения и записи.
MouseIcon	Возвращает строку, которая представляет полное имя пути настраиваемого значка, назначенного для управления. Только для чтения.
MousePointer	Возвращает или задает набор integer, который указывает тип указателя, отображаемого при позиционировании пользователя мыши над определенным объектом. Для чтения и записи.
SelectionMargin	Возвращает или задает boolean, который указывает, может ли пользователь выбрать строку текста, щелкнув в регионе слева от текста. Для чтения и записи.
SelLength	Возвращает или задает long, представляющие количество символов, выбранных в текстовой части ComboBox. Для чтения и записи.
SelStart	Возвращает или задает длинный, который представляет отправную точку выбранного текста, или точку вставки, если текст не выбран. Для чтения и записи.
SelText	Возвращает или задает строку, представляюную выбранный текст управления. Для чтения и записи.
ShowDropButtonWhen	Возвращает или задает значение fmShowDropButtonWhen, которое указывает, когда нужно показать кнопку падения для ComboBox. Для чтения и записи.
SpecialEffect	Возвращает или задает набор integer, который указывает внешний вид объекта. Для чтения и записи.
Style	Возвращает или задает набор integer, который указывает, как пользователь может выбрать или установить значение управления. Для чтения и записи.
Text	Возвращает или задает строку, которая указывает текст в ComboBox, изменяя выбранную строку в области управления. Для чтения и записи.
TextAlign	Возвращает или задает набор integer, который указывает, как текст выравнивается в области управления. Для чтения и записи.
TextColumn	Возвращает или задает вариант, который идентифицирует столбец в ComboBox для отображения пользователю. Для чтения и записи.
TextLength	Возвращает длинный текст, который представляет длину текста в области редактирования ComboBox. Только для чтения.
TopIndex	Возвращает или задает long, представляющие индекс элемента, отображаемого в верхней позиции в части списка ComboBox. Для чтения и записи.
Значение	Возвращает или задает вариант, который указывает значение в BoundColumn выбранных в настоящее время строк. Для чтения и записи.

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Синтаксис полного кода VBA Excel, применяемого для сортировки данных в таблицах и диапазонах:

Синтаксис сокращенного кода VBA Excel, применяемого для сортировки данных с параметрами по умолчанию:

Expression – выражение, возвращающее объект Worksheet, например:

Расшифровка кода

1. Expression.Sort – метод Sort объекта Worksheet возвращает объект Sort.

Объект Sort – это объект, представляющий сортировку диапазона данных.

2. .SortFields.Clear – метод SortFields объекта Sort возвращает коллекцию объектов SortFields. Метод Clear объекта SortFields удаляет все существующие объекты SortField.

Объект SortField содержит все сведения о параметрах сортировки для заданного рабочего листа.

3. .SortFields.Add Key, SortOn, Order, DataOption – метод Add объекта SortFields создает и возвращает новый экземпляр объекта SortField с заданными параметрами.

Параметры метода Add объекта SortFields:

Key – обязательный параметр, который задает значение ключа для сортировки. Тип данных – Range. Обычно указывается первая ячейка столбца при сортировке по строкам или первая ячейка строки при сортировке по столбцам. Сортировка диапазона будет осуществлена по данным столбца (строки), первая ячейка которого указана в качестве ключа.

SortOn – необязательный параметр, который задает критерий сортировки (по какому свойству ячеек производится сортировка).

Значения, которые может принимать SortOn:

Константа	Значение	Описание
SortOnValues	0	сортировка по значению (значение по умолчанию)
SortOnCellColor	1	сортировка по цвету ячейки
SortOnFontColor	2	сортировка по цвету шрифта
SortOnIcon	3	сортировка по иконке*

* Иконки (значки) могут быть заданы ячейкам при условном форматировании диапазона.

Order – необязательный параметр, задающий порядок сортировки (по возрастанию или по убыванию).

Значения, которые может принимать Order:

Константа	Значение	Описание
xlAscending	1	сортировка по возрастанию (значение по умолчанию)
xlDescending	2	сортировка по убыванию

DataOption – необязательный параметр, который задает способ сортировки текста.

Значения, которые может принимать DataOption:

Константа	Значение	Описание
xlSortNormal	0	числовые и текстовые данные сортируются отдельно (значение по умолчанию)
xlSortTextAsNumbers	1	текстовые данные рассматриваются для сортировки как числовые

4. .SetRange [Range] – метод SetRange объекта Sort задает диапазон (таблицу), в котором выполняется сортировка.

5. .Header = [xlGuess, xlYes, xlNo] – свойство Header объекта Sort указывает, является ли первая строка таблицы строкой заголовков (шапкой).

Значения, которые может принимать свойство Header:

Константа	Значение	Описание
xlGuess	0	Excel сам определяет, есть ли строка заголовков
xlYes	1	строка заголовков есть, сортировка ее не затрагивает
xlNo	2	строки заголовков нет (значение по умолчанию)

6. .MatchCase = [True, False] – свойство MatchCase объекта Sort указывает, как учитывать регистр при сортировке.

Значения, которые может принимать свойство MatchCase:

Константа	Значение	Описание
False	0	регистр не учитывается (значение по умолчанию)
True	1	сортировка с учетом регистра

7. .Orientation = [xlTopToBottom, xlLeftToRight] – свойство Orientation объекта Sort задает ориентацию для сортировки.

Значения, которые может принимать свойство Orientation:

Константа	Значение	Описание
xlTopToBottom	1	сортировка по стокам (значение по умолчанию)
xlLeftToRight	2	сортировка по столбцам

8. .Apply – метод Apply объекта Sort выполняет сортировку диапазона в соответствии с примененными параметрами.

Примеры сортировки

Таблица для примеров

Сортировка по одному столбцу

Краткая запись кода VBA Excel для сортировки диапазона по первому столбцу с параметрами по умолчанию:

Создайте пользовательскую форму UserForm1 и разместите на ней поле со списком ComboBox1. Используйте метод AddItem для заполнения элемента управления значениями:

Скопируйте код и запустите его выполнение, на открывшейся форме раскройте поле со списком, в результате увидите, что элемент управления ComboBox1 заполнен соответствующими значениями:

Заполнение ComboBox значениями из массива

Для заполнения элемента управления ComboBox значениями из массива будем использовать свойство поля со списком List и функцию Array:

. ComboBox1 . List = Array ( "Кружка" , "Стакан" , "Бокал" , "Пиала" , "Фужер" )

Результат выполнения кода будет таким же, как и на предыдущем изображении.

Таким же образом можно использовать не только функцию Array, но и переменную массива, предварительно объявленную и заполненную значениями:

Заполнение ComboBox значениями из ячеек

Для заполнения поля со списком значениями из диапазона ячеек рабочего листа будем использовать свойство комбинированного списка RowSource, предварительно заполнив диапазон «A1:A5» активного листа уже известными значениями:

Чтобы присвоить элементу управления ComboBox значения из диапазона ячеек любого рабочего листа, добавьте ссылку на него перед наименованием диапазона, например, замените «A1:A5» на «Лист1!A1:A5», и поле со списком будет заполнено значениями ячеек «A1:A5», расположенных на листе с именем «Лист1». Имя листа берется из наименования ярлыка.

Более подробно о заполнении данными ComboBox можно прочитать в статье о заполнении ListBox, так как оба эти элемента управления используют одинаковые способы заполнения.

Иногда возникает необходимость заполнения элементов управления ListBox и ComboBox уникальными значениями из диапазона ячеек с повторяющимся содержимым. Смотрите, как отсортировать уникальные элементы из списка с помощью объектов Collection и Dictionary.

Вы можете скачать файл Excel с представленными выше примерами. Файл упакован в ZIP-архив. Для проверки работоспособности кода, непосредственно в редакторе VBA помещайте курсор внутри тела каждой процедуры и нажимайте кнопку «Run Sub».

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

Не получился ни один из предложенных вариантов( С чем это может быть связано?

Спасибо за примеры, на одном все получилось. Чуток лишнее удалил и все ОК.
Потому что пример привязан к тестовому объекту.
Я тоже не мог понять, при отсутствии навыков.
Но вот четко к полю привязку по примеру удалось сделать.
Аналогично в powerpoint вставка поля со списком

Существуют разные способы сортировки данных в Microsoft Excel. Под вкладкой Excel Data находится значок сортировки, откуда вы можете быстро отсортировать данные и получить результаты. Почему тогда нужно писать сложный код VBA?

Вы должны помнить это каждый раз, коды VBA существуют для автоматизации вещей. Предположим, у вас есть данные, которые время от времени обновляются. Может быть, каждый день, еженедельно, ежемесячно и т. Д., И вам нужны ваши данные определенным образом. В таких случаях можно использовать функцию VBA SORT, которая станет удобным инструментом для вашей справки.

VBA имеет метод Range.Sort для сортировки данных. Где Range указывает диапазон ячеек, которые мы хотим отсортировать в порядке возрастания или убывания.

Синтаксис для Range.Sort приведен ниже:

Ключ - столбец / диапазон, который нужно отсортировать. Ex. Если вы хотите отсортировать ячейки A1: A10, вы должны упомянуть Range (A1: A10)
Порядок - это параметр, который позволяет сортировать данные в порядке возрастания или убывания.
Заголовок - это параметр, который указывает, имеет ли ваш столбец / диапазон заголовки или нет.

Этих трех параметров достаточно для нашей работы. Однако есть некоторые другие параметры, такие как MatchCase, SortMethod и т. Д., Которые вы всегда можете изучить и посмотреть, как они работают.

Как использовать функцию сортировки Excel VBA?

Мы узнаем, как сортировку VBA можно использовать для сортировки столбца без заголовков, столбца с заголовками и нескольких столбцов с примерами в Excel.

Вы можете скачать этот шаблон VBA Sort Excel здесь - Шаблон VBA Sort Excel

Функция сортировки VBA - пример № 1

Сортировка одного столбца без заголовка

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

Выполните следующие шаги, чтобы использовать функцию сортировки в VBA.

Шаг 1: Определите новую sup-процедуру в модуле и создайте макрос.

Код:

Шаг 2: Используйте функцию Range.Sort для сортировки этого столбца в порядке возрастания.

Код:

Здесь вы даете диапазон, начиная с ячейки A1 до последней использованной / непустой ячейки (см. Функцию .End (xlDown)) для функции Range.Sort.

Шаг 3: Теперь введите значения аргумента.

Код:

Как мы уже обсуждали ранее, Key, Order и Header являются важными и необходимыми аргументами, которые необходимо предоставить. В качестве начального диапазона столбца мы указали Range («A1»), который нам нужно отсортировать. Порядок предоставляется в порядке возрастания, а заголовок - как нет (что означает, что столбец не имеет заголовка).

Этот код проверяет все непустые ячейки, начиная с A1, а затем сортирует их в порядке возрастания, начиная с ячейки A1.

Шаг 4: Запустите этот код, нажав F5 или кнопку Run вручную и посмотрите результат.

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

Функция сортировки VBA - пример № 2

Сортировка одной колонки с заголовком

Предположим, у вас есть столбец с заголовком, как показано ниже. И вы хотели отсортировать этот столбец в порядке возрастания или убывания.

Выполните следующие шаги, чтобы использовать функцию сортировки в VBA.

Шаг 1: Определите новую подпроцедуру в новой модели для хранения макроса.

Код:

Шаг 3: Используйте Range («A1»). Сортируйте перед приведенной выше строкой кода, чтобы сделать ее функцией сортировки.

Код:

Шаг 4: Введите Key1 как Range («A1») для сортировки данных из ячейки A1, Order1, чтобы отсортировать данные в порядке возрастания или убывания, и Header как « Да», чтобы система знала, что первая строка является заголовком в вашем данные.

Код:

Шаг 5: Запустите этот код, нажав F5 или кнопку Run вручную и посмотрите результат.

Здесь данные из примера № 2 данной рабочей книги Excel сортируются в порядке возрастания, учитывая, что они имеют заголовок. Это означает, что при сортировке этих данных первая строка (которая содержит имя Emp ) игнорируется, поскольку она рассматривается как заголовок для этих данных в столбце A.

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

Шаг 6: Измените order1 на нисходящий, чтобы отсортировать данные в порядке убывания.

Код:

Шаг 7: Запустите код и посмотрите вывод, как показано ниже.

Вы можете видеть, что данные отсортированы в порядке убывания.

Функция сортировки VBA - пример № 3

Сортировка нескольких столбцов с заголовками

До сих пор мы рассмотрели, как сортировать данные одного столбца в порядке возрастания или убывания (без заголовка и с заголовком). Что если у вас есть данные, которые нужно отсортировать по нескольким столбцам? Можно ли написать код для того же?

Ответ: «Да, конечно, это можно сделать!»

Предположим, у вас есть данные, указанные ниже:

Вы хотели отсортировать эти данные сначала по Emp Name, а затем по Location. Выполните следующие шаги, чтобы увидеть, как мы можем кодировать его в VBA.

Шаг 1: Определите новую подпроцедуру для добавления макроса в новый модуль.

Код:

Шаг 2: Используйте оператор With… End With, чтобы добавить несколько условий сортировки в одном цикле.

Код:

Шаг 3: Теперь используйте SortFields.Add, чтобы добавить несколько условий сортировки в одном листе.

Код:

Шаг 4: Определите диапазон листов для сортировки и заголовок На следующем шаге.

Код:

Шаг 5: Используйте .Apply, чтобы применить все это под оператором with и закрыть цикл, пишущий End With.

Код:

Шаг 6: Запустите этот код, нажав F5 или кнопку Run вручную, и увидите результат.

В этом коде ActiveSheets.Sort помогает системе идентифицировать лист, по которому должны быть отсортированы данные. SortFields.Add позволяет добавлять два условия сортировки с их порядком (по возрастанию в обоих случаях). SetRange позволяет системе установить диапазон от A1 до C13. Вы также можете увеличить этот диапазон. Операторы Apply позволяют системе применять все изменения, сделанные в цикле With.

Наконец, вы получите данные, которые отсортированы по имени Emp сначала, а затем по местоположению.

То, что нужно запомнить

Под сортировкой VBA вы можете создавать именованные диапазоны вместо ссылок на ячейки и использовать их. Ex. Если вы создали именованный диапазон для ячейки A1: A10 как «EmpRange», вы можете использовать его в Range.Sort, например Range («EmpRange»).
Вы можете сортировать данные как по возрастанию, так и по убыванию, как в Excel.
Если вы не уверены, есть ли у ваших данных заголовок или нет, вы можете использовать xlGuess в разделе заголовка, чтобы система могла угадать, является ли первая строка данных заголовком или нет.