Выпадающий список из диапазона, с учётом применённых стилей в ячейках.

[ximik]

Доброго времени суток!
Господа! Помогите, пожалуйста, с выпадающим списком.


Коротко:
Данные --> Проверка --> Диапазон ячеек (+Условие)
Есть некий диапазон с текстовыми данными.
К некоторым ячейкам применён 'стиль1', к другим 'стиль2'.
В выпадающем списке показывать те, которые удовлетворяют условию 'стиль1'.


Подробно:
Есть некоторое количество заводов с индивидуальным для каждого перечнем изделий (образно список до 400).
Какие-то позиции на данный момент актуальны, какие-то нет.
Чтобы в выпадающем списке не растягивать простыню на все позиции, ограничили её через доп.условие в соседней колонке (Пример во вложении). В начале месяца определяем 20-25 позиций и спокойно работаем с "облегчёнными списками".
Но(!) хочется эргономичности, чтоб глаз радовался, да и ячейки 'избыточные' не образовывались, а по сему — возникло дикое желание просто красить нужные позиции в цвет — быстро, красиво, понятно. Но как прикрутить это в условие диапазона — не понятно. Помогите, пожалуйста.

P.S. Уровень владения любыми таблицами, макросами = 'стремится к нулю'. Химией занимаюсь ¯\_(ツ)_/¯

[eeigor]

Вы всё сделали правильно. Если надо красить, то примените к диапазону "Выбрано (актуально) - Изделие" условное форматирование: краситься будет само. И продолжайте выбирать или отменять выбор, как и раньше, но используя нолики и единички. Это гораздо проще, чем тыкаться в палитру.

Файл обновлен

[ximik]

Спасибо за оперативность!

Так-то да, красить через условное форматирование понятно, использую.
Но, как раз хочется уйти от лишних колонок (где будут прописано 0/1 или иные условия "да"/"нет").

Хочется просто 1 колонку на завод с позициями.
Хочется... в начале месяца покрасить всё в 'стиль_красный' и быстренько через Сtrl выделить нужные ячейки и жмакнуть 'стиль_синий'; следующий завод -повторить- и т.д.

Да, можно аналогично сделать с "0/1", размножить нули, проставить единицы, скрыть эти колонки и радоваться красивому...
но:
- ненужные колонки (а их много, заводов много, позиций много == много ячеек, избыточность информации вроде как)
- неудобно выбирать: смотришь и читаешь названия в одной колонке - проставляешь маркер в другой, глаза разбегаются...   Номенклатуры объёмные.

Может можно как-то стиль ловить? м?

UPD: И потом этот весь лист используется как источник для всяких vlookup'ов, лишние колонки мешают.

[eeigor]

Ну, можно, но нужен макрос, который и будет анализировать стиль ячейки.
Так же, условие Если, в нём GetStyle(), и если имя стиля равно заданному, то Да, иначе Нет.

Нет, проще сразу вернуть данные, отвечающие условию...

[ximik]

Если стандартными возможностями нет, значит нет. Отрицательный он-то тоже результат. Спасибо за время!

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

[eeigor]

Никто не сказал "Нет".
Посмотрите.
Выбираются только те записи, которые имеют стиль "_текст синий" (как Вы его обозвали). Записи, исключенные из набора, имеют другой стиль: "_текст серый".

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

Вы сначала попробуйте. Всё как Вы хотели.
В окне Проверка введите формулу:
 FILTERDATABYSTYLE(B12:B16;"_текст синий")
Диапазон укажите требуемый, имя стиля тоже. Больше ничего не требуется. "FILTERDATABYSTYLE" означает "отфильтровать данные по стилю" (в данном случае по имени "_текст синий"). Если стиль другой, то его сначала нужно создать.
Кстати, пустая строка из списка выбора тоже исчезла.
Изучать макрос совсем необязательно. У Вас есть рабочий пример. И мало ли вокруг Вас чёрных ящиков?.. Пусть будет ещё один маленький.

Приведу и макрос

Код Выделить Развернуть

Option VBASupport 1

Function FilterDataByStyle(oVBARange As Object, sStyleName$)
Dim oCellRange As Object, oCell As Object
Dim n%, aData()
Dim i&

On Local Error GoTo Failed
oCellRange = oVBARange.CellRange
For i = 0 To oCellRange.Rows.Count - 1
oCell = oCellRange.getCellByPosition(0, i)
If oCell.CellStyle = sStyleName Then
If n = 0 Then
ReDim aData(n)
Else
ReDim Preserve aData(n)
End If
aData(n) = oCell.String
n = n + 1
End If
Next
FilterDataByStyle = aData

Failed:
End Function

[ximik]

Спасибо eeigor!

Ящик вина этому господину!

[eeigor]

Оптимизация всегда идёт следом. Я чуть изменил код и проверил на списках под 400 позиций: мгновенно.
Прим. Инструкция ReDim Preserve теперь вызывается не в цикле, а один раз. Существенное ускорение.
Если ничего не выбрано, то список будет содержать одну строку: "(пусто)".

Код Выделить Развернуть

Option VBASupport 1


Function FilterDataByStyle(oVBARange As Object, sStyleName$)
Dim oCellRange As Object, oCell As Object
Dim i&, nCount&, n&, aData$()

On Local Error GoTo Failed
oCellRange = oVBARange.CellRange
nCount = oCellRange.Rows.Count
ReDim aData(nCount - 1)

For i = 0 To nCount - 1
oCell = oCellRange.getCellByPosition(0, i)
If oCell.CellStyle = sStyleName Then
aData(n) = oCell.String
n = n + 1
End If
Next

If n = 0 Then
FilterDataByStyle = "(пусто)"
Else
If n < i Then
ReDim Preserve aData(n - 1)
End If
FilterDataByStyle = aData
End If

Failed:
End Function

Код "толерантен" к ошибкам: он их попросту игнорирует.

Если Вы уже наполнили свой файл содержанием (когда будете смотреть этот пост), то надо просто скопировать и заменить свою процедуру этой. Новый файл тоже прилагается. Но можно только заменить, для чего надо открыть программный модуль.

[eeigor]

Вопрос больше к Михаилу (@mikekaganski).
У нас есть в планах сделать возможной передачу в UDF объекта CellRange вместо его свойства DataArray, как это реализовано сейчас? Существенное ограничение.
В примере выше по этой причине использован объект VBARange, получаемый в режиме VBASupport.

[sokol92]

сделать передачу в UDF объекта CellRange вместо его свойства DataArray

Увы, это невозможно. Многие UDF уже написаны с учетом имеющихся на сегодня интерфейсов.
Интерфейсы - "пожизненный" контракт, их нельзя менять, можно только добавлять новые методы и свойства.

[eeigor]

Это очень плохо. Из-за разной обработки опциональных параметров в зависимости от режима (нативный, Compatible, VBASupport) подобные процедуры (как у меня выше в примере) приходится выносить в отдельный модуль.

[sokol92]

Пока еще не ввели налог на число модулей...