как сделать выпадающий лист?

[RainMan]

Здравствуйте!
Почитал несколько листов данной ветки но не нашел ответа на свой вопрос.
У меня вот какая проблема.
Лист1: в данном листе находится перечень цифр в столбце с А1 до А10 и этому диапазону дано имя "num". Однако цифры в этом диапазоне могут располагаться через ячейку, то есть в диапазоне от 1 до 10 ячейка с номером 5 может быть пустой, или несколько ячеек пустой.

Лист2: в данном листе необходимо вставить в ячейку тип Validation чтобы получлась ячейка с выпадающей листом того что есть в диапазоне "num на Листе1 но без пробелов, чтобы цыфры шли один за другим, без учета пробелов.

Я написал вот такой код:

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

Sub predpr()
        Worksheets("Лист1").Activate
        Set r = Range("num")
        For n = 1 To r.Rows.Count
        a = a & "," & r.Cells(n, 1).Value
    Next n

Worksheets("Лист2").Activate
Range("A1").Activate
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=a
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
Range("A1").Select
End Sub

Нутром чувствую что это все криво написано, но работает вроде как нужно, но проблема возникает тогда если цифры в ячейках заменить словами и такими что вся их общая суммарная длинна со всех 10 ячеек больше 255 символов! сама переменная "а" воспринимает всю длину но когда делается Validation то строка обрезается до длины 255 символов.

Может кто-то подскажет как более грамотно реализовать считывание данных с друго-го листа и вставку в выпадающий лист текущего листа?

вот что есть на входе:
"ФФФФФФФФФФФ"

"ВВВВВВВВВВВВВ"

"ККККККККККККККК"
"ННННННННННННННН"

На выходе нужно получить выпадающий лист без пробелов в ячейках:
"ФФФФФФФФФФФ"
"ВВВВВВВВВВВВВ"
"ККККККККККККККК"
"ННННННННННННННН"


Спасибо.

[pashulka]

Если Вы используете об'ект Validation, то выбрав в качестве проверки - Список (xlValidateList), необходимо в качестве источника указать либо диапазон ячеек, либо именованную формулу, которая также возвращает ссылку на диапазон ячеек, либо строку. В последнем случае существует ограничение на 255 символов. Поэтому, возможно имеет смысл, использовать элемент управления ActiveX - ComboBox, который можно заполнить программно, например, при активации рабочего листа - "Лист2" Размеры этого элемента управления можно подогнать под необходимые и связать его с нужной ячейкой - "A1" посредством использования свойства LinkedCell

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

Private Sub Worksheet_Activate()
ComboBox1.Clear
For Each iCell In Application.Range("num")
    If iCell.Value <> "" Then _
       ComboBox1.AddItem iCell.Value
Next
End Sub

Расположить это событие нужно в модуле рабочего листа "Лист2", после создания поля со списком (меню Вид - пункт Панели инструментов - команда Элементы управления - кнопка Поле со списком)

[RainMan]

ActiveX тут не подойдет потому что я хочу всему выделенному диапазону (то есть каждой ячейке этого диапазона) присвоить такой выпадающий лист. Этих ячеек будет очень много а рисовть ComboBox для них это не годится...
есть другой вариант но не знаю как его осуществить..

можно список скопировать в другое место, циклом поднять все строки, чтобы получился сплошной список и тогда делать ссылку на такой список...
То есть нужно скопировать оригинальный список, обработать его (то есть поднять все строки, что бы не было пустых ячеек) и узнать диапазон хорошего списка чтобы потом можно было на него сослаться.
Как это можно осуществить?

Спасибо.

[pashulka]

ActiveX тут не подойдет ... © RainMan 2005 г.

Отнюдь. Даже, несмотря на изменившиеся условия, ничто не мешает предварительно создать нестандартное диалоговое окно UserForm, Лист диалога или Панель инструментов (содержащее поле со списком), которое будет отображаться при выделении/активации нужных ячеек. P.S. Только о использовании свойства LinkedCell придётся забыть.

[Avsha]

Попробуйте такой вариант...
Пусть диапазон "mas_A" - исходный список, располагается A1:A10
а диапазон "mas_B" - список без пробелов, располагается B1: B....
Тогда после выполнения кода, значения функции списка для ячеек нужно уже брать из диапазона "mas_B"

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

Private Sub CommandButton1_Click()
  p = 0
  Range("B:B").ClearContents
  For Each iCell In Application.Range("mas_A")
    If iCell.Value <> "" Then
        p = p + 1
        Cells(p, 2) = iCell.Value
    End If
  Next
  ActiveWorkbook.Names.Add Name:="mas_B", RefersToR1C1:=Range("B1", Cells(p, 2))
End Sub

[pashulka]

Недостатки вышеопубликованного способа IMHO :
- использование дополнительных ячеек, так где без них можно обойтись.
- вероятность преднамеренного/непреднамеренного удаления/добавления данных в этом диапазоне, что породит появление некорректного списка. Хотя, если использовать ячейки скрытого рабочего листа, то вероятность подобного развития события, можно свести к минимуму.
- необходимость нажатия кнопки, для обновления списка, в случае изменения данных в первоначальном диапазоне. Впрочем, и этот недостаток также можно устранить, если использовать соответствующее событие/свойство рабочего листа, "реагирующее" на изменение данных в ячейках.
Однако в этом случае список будет "обновляться" при каждом изменении данных, в предложенном мною варианте, процесс заполнения списка, будет необходим только при отображении формы.

[Avsha]

Да, pashulka прав, существуют некоторые недостатки, которые легко устраняются.

Вот например запуск обновления "mas_B" можно выполнять автоматически в подпрограмме рабочего листа:

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

     ' при изменении выделения ячеек на Листе, где находиться исходный массив "mas_A"
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    CommandButton1_Click
End Sub
      ' или, когда переходите на другой лист ...
Private Sub Worksheet_Deactivate()
 CommandButton1_Click
End Sub

Private Sub Worksheet_Change(ByVal Target As Range) - не пойдет, так как я в коде пишу в ячейки столбца B, что вызывает повторное срабатывание Worksheet_Change.

Ну а достоинства вы сами оцените, когда будете использовать стандатный механизм выбора значения из списка
(меню Данные\Проверка\ ТипДанных:=<Список>\ Источник:= F3 - <Mas_B>).

[pashulka]

Конечно можно создавать себе трудности, для того чтобы героически их преодолевать, но в данном конкретном случае, без этого легко можно обойтись. Вдобавок, остаются вопросы, касающиеся возможности изменения диапазона Mas_B. Что касается самой возможности обновления диапазона, например, используя событие Worksheet_Change, то это возможно.
Событие Worksheet_Deactivate() будет запускаться при деактивации рабочего листа, но нет никакой гарантии, что при этом будет активирован именно нужный рабочий лист, т.е. обновление может происходить даже тогда, когда нам этого не нужно.

Об'ект Validation предназначен для проверки вводимых данных с клавиатуры, т.е. самое банальное копирование (и не только) позволит изменить данные в подобных ячейках, даже если они не соответствуют проверке. Конечно можно периодически проверять корректность заполнения подобных ячеек, но это опять преодоление собственноручно созданных трудностей. Воспользовавшись моим вариантом всего этого можно избежать.

[RainMan]

Спасибо за помощь!
все прекрасно работает, и даже лучше чем ожидалось