Developing.ru

В ячейке сложной формулой создаётся список допустимых строк для другой ячейки
типа: aa;bbb;e5;g88;a44;u;tttt
Хочется использовать этот список для создания выпадающего списка в другой ячейке,
используя Validation List.
При банальном указании в .Validation.add Formula1:=MyCell.Value
через раз вместо списка получается просто один элемент - строка с ;

При явном указании этого же списка значений через интерфейс - всё всегда работает

Excel 2003 eng SP3 (With rus MUI)
Хочется в идеале вообще без VBA сослаться в интерфейсе проверки данных на
формулу, cсылающуюся на мою ячейку со значениями для получения выпадающего списка.
Можно переформулировать проблему. Как формулой из строки создать ранг(массив)
Вариант с записью элементов строки во временные ячейки рабочего листа меня не устраивает.
Как строить связанные проверочные списки я тоже знаю

Интересно решение именно поставленной задачи

Всё оказалось очень просто. Не заметил что у меня в ячейке формируется список с разделителем ";" а не ","
Итак: В интерфейсе Данные->Проверка пишем AAA;BBB;CCC
В VBA пишем .Validation.add ...Formula1:="AAA,BBB,CCC"
Макрорекордер кстати пишет неправильно. C ";"

Вопрос снят.

Вопрос снят.

Зато появились другие вопросы...

Тоже столкнулся с ошибкой макрорекордера (когда он пишет ; вместо ,)

В этом топике обнаружил решение проблемы, заменил в коде
Formula1:="Выполнено; Не выполнено" на Formula1:="Выполнено, Не выполнено", и всё заработало.

Получился такой код:

Код: Выделить всё

Sub FormatConfirmationCell(ByRef ce As Range)
    With ce
        .HorizontalAlignment = xlCenter
        With .Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= _
                 xlBetween, Formula1:="Выполнено, Не выполнено"
                 
            .IgnoreBlank = True: .InCellDropdown = True
        End With
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Выполнено"""
        .FormatConditions(1).Interior.ColorIndex = 35    '4
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Не выполнено"""
        .FormatConditions(2).Interior.ColorIndex = 38    '3
    End With
End Sub

Но радость была недолгой.

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

Выяснилось, что если список разделять символом "," , то в первых нескольких ячейках, для которых он применяется, выпадающий список состоит из 2 элементов (как и должно быть), а во всех остальных - из одного "Выполнено, Не выполнено"

Если же использовать разделитель ";" (как пишет макрорекордер), то всё происходит с точностью до наоборот - в первых ячейках один элемент "Выполнено; Не выполнено", а в остальных - всё как надо.

Проблемы с выпадающим списком появляются с того места, где в той же строке присутствует гиперссылка.
Со следующей после гиперссылки строки и меняется поведение VBA, формирующего выпадающий список.

Кто-нибудь может объяснить, как с этим бороться?

Вот пример кода. Запустите макрос test

Если в строке Formula1:=ce.Value & ", Выполнено, Не выполнено"
поменять "," на ";" - происходит всё наоборот.

Код: Выделить всё

Sub [B]test[/B]()
    Workbooks.Add
    For i = 3 To 30
        Cells(i, 2) = "Строка " & i
        FormatConfirmationCell Cells(i, 2)
        If i Mod 10 = 0 Then
            Cells(i, 5).Value = "www.ya.ru"
            Convert_Range_into_HLinks_test Cells(i, 5)
        End If
    Next
End Sub

Sub FormatConfirmationCell(ByRef ce As Range)
    With ce
        .HorizontalAlignment = xlCenter
        With .Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= _
                 xlBetween, Formula1:=ce.Value & ", Выполнено, Не выполнено"
            .IgnoreBlank = True: .InCellDropdown = True
        End With
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Выполнено"""
        .FormatConditions(1).Interior.ColorIndex = 35    '4
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Не выполнено"""
        .FormatConditions(2).Interior.ColorIndex = 38    '3
    End With
End Sub

Sub Convert_Range_into_HLinks_test(ByRef ra As Range)
    'преобразует текст всех непустых ячеек диапазона в гиперссылки
    Dim selcell As Range
    On Local Error Resume Next
    For Each selcell In ra.Cells
        If Len(selcell.Value) > 0 Then
            HLaddr = "http://" & Trim$(selcell.Value)
            selcell.Parent.HyperLinks.Add Anchor:=selcell, Address:=HLaddr
        End If
    Next
End Sub

В Excel 2K не проявляется этот баг.
Т.е. макрорекордер, то конечно пишет не тот разделитель, но он увы не в силах пропарсить еще и строку, тогда как сам Excel (в диалоговом окне Данные/Проверка) подменяет системный разделитель на национальный.
Но если у Range есть свойства Formula и FormulaLocal, то у Validation только Formula1. Нестыковочка....

Ну а по существу дела: в Excel 2K у меня приведеденный макрос стабильно дает выпадающий список, независимо от наличия гиперссылок.

У меня Excel 2003 rus, WinXPsp2

Пора переходить обратно на Office2000

Вопрос в принципе решил, сначала формирую выпадающие списки, а потом уже делаю гиперссылки, но... всё-таки непонятно, почему такое происходит.

Может, после вставки каждой гиперссылки можно программно изменять системный разделитель ?

Developing.ru

Validation List из значения ячейки

Validation List из значения ячейки

Re: Validation List из значения ячейки

Re: Validation List из значения ячейки

Re: Validation List из значения ячейки

Re: Validation List из значения ячейки