Выпадающий список в ячейках Excel, с изменяемым диапазоном.

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Выпадающий список в ячейках Excel, с изменяемым диапазоном. (Макросы/Sub)

lopuxi

Дата: Понедельник, 16.11.2020, 17:52 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007, 2013

Добрый день гуру excel!!!
Я не так часто пишу что-то в макросах, однако сейчас встала такая вот задача и я столкнулся с проблемой. Поиски решений в интернете не дали результата. Хотя несколько близких тем я нашел.

Здесь вот чувак бился над похожей задачей, но как он ее разрешил я все же разобрать не смог. https://stackoverflow.com/questio....-variab

Я опишу задачу в двух словах. У меня есть книга, в ней есть два листа. Лист - "Карточка Клиента" с программным именем Client_Card и Лист - "Данные" c программным именем staff_list.
На Листе1 есть табличка, в нескольких ее ячейках я хочу вывести выпадающие списки.
На листе2 храниться данные для выпадающего списка.

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

[vba]

Код

With Client_Card.Range(Client_Card.Cells(14, 3), Client_Card.Cells(27, 3)).Validation

[/vba]

Я создал в книге с примером код. Как раз тот который работать не хочет.

Я пробовал просто вставлять в

[vba]

Код

.... Formula1:=staff_list.Range(staff_list.Cells(2, 1), staff_list.Cells(9, 1)).Value 'но так оно не работает.

[/vba]

[vba]

Код

   With Client_Card.Cells(14, 3).Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=staff_list.Range(staff_list.Cells(2, 1), staff_list.Cells(9, 1)).Value
    End With

[/vba]

Пробовал через переменную... но так тоже не работает

[vba]

Код

Private Sub CommandButton1_Click()
Dim bbbb As String

bbbb = staff_list.Range(staff_list.Cells(2, 1), staff_list.Cells(9, 1)).Value '

    With Cells(14, 3).Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=bbbb"
    End With


End Sub

[/vba]

Я подозреваю что список этот, в переменной необходимо подать через запятую... то есть видимо его надо прогнать через массив, где каждому значению подставить запятую и сформировать конечную переменную со всеми значениями через запятую.
Но я пока не стал так делать, в надежде что есть более простой способ, а я просто туплю...

В обще я хочу формировать данный выпадающие список на листе "Карточка клиента", при запуске книги. Но это потом, сейчас хотелось бы его заставить код работать

Код

With Client_Card.Range(Client_Card.Cells(14, 3), Client_Card.Cells(27, 3)).Validation

[/vba]

Я создал в книге с примером код. Как раз тот который работать не хочет.

Я пробовал просто вставлять в

[vba]

Код

.... Formula1:=staff_list.Range(staff_list.Cells(2, 1), staff_list.Cells(9, 1)).Value 'но так оно не работает.

[/vba]

[vba]

Код

[/vba]

Пробовал через переменную... но так тоже не работает

[vba]

Код

О_о ...и так можно было?

Ответить

СообщениеДобрый день гуру excel!!!
Я не так часто пишу что-то в макросах, однако сейчас встала такая вот задача и я столкнулся с проблемой. Поиски решений в интернете не дали результата. Хотя несколько близких тем я нашел.

Здесь вот чувак бился над похожей задачей, но как он ее разрешил я все же разобрать не смог. https://stackoverflow.com/questio....-variab

Я опишу задачу в двух словах. У меня есть книга, в ней есть два листа. Лист - "Карточка Клиента" с программным именем Client_Card и Лист - "Данные" c программным именем staff_list.
На Листе1 есть табличка, в нескольких ее ячейках я хочу вывести выпадающие списки.
На листе2 храниться данные для выпадающего списка.

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

[vba]

Код

With Client_Card.Range(Client_Card.Cells(14, 3), Client_Card.Cells(27, 3)).Validation

[/vba]

Я создал в книге с примером код. Как раз тот который работать не хочет.

Я пробовал просто вставлять в

[vba]

Код

.... Formula1:=staff_list.Range(staff_list.Cells(2, 1), staff_list.Cells(9, 1)).Value 'но так оно не работает.

[/vba]

[vba]

Код

[/vba]

Пробовал через переменную... но так тоже не работает

[vba]

Код

Автор - lopuxi
Дата добавления - 16.11.2020 в 17:52

Kuzmich

Дата: Понедельник, 16.11.2020, 19:17 | Сообщение № 2

Группа: Проверенные

Ранг: Ветеран

Сообщений: 713

Репутация: 157 ±

Замечаний: 0% ±

Excel 2003

Цитата

Я создал в книге с примером код.

А где этот пример?

Цитата

Я пробовал просто вставлять в

.... Formula1:=staff_list.Range(staff_list.Cells(2, 1), staff_list.Cells(9, 1)).Value 'но так оно не работает.

А так попробуйте
[vba]

Код

.... Formula1:="=" & staff_list.Range(staff_list.Cells(2, 1), staff_list.Cells(9, 1)).Address & ""

[/vba]

Сообщение отредактировал Kuzmich - Понедельник, 16.11.2020, 19:36

Ответить

lopuxi

Дата: Вторник, 17.11.2020, 11:46 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007, 2013

Этот пример не сработал я пробовал другие. На котором остановился, тот файл и загрузил.

Цитата Kuzmich, 16.11.2020 в 19:17, в сообщении № 2 ()

А где этот пример?

Только что попробовал так. Выдает ошибку. сделал скриншот.

Цитата Kuzmich, 16.11.2020 в 19:17, в сообщении № 2 ()

А так попробуйте
.... Formula1:="=" & staff_list.Range(staff_list.Cells(2, 1), staff_list.Cells(9, 1)).Address & ""

К сообщению приложен файл: 4859580.png (57.7 Kb)

О_о ...и так можно было?

Ответить

Pelena

Дата: Вторник, 17.11.2020, 11:51 | Сообщение № 4

Группа: Админы

Ранг: Местный житель

Сообщений: 19404

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

Цитата lopuxi, 17.11.2020 в 11:46, в сообщении № 3 ()

тот файл и загрузил

Файл не прикрепился

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

lopuxi

Дата: Вторник, 17.11.2020, 11:54 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007, 2013

А вот, оно что... странно, я его точно прикреплял. Попробую сейчас еще раз.

Цитата Pelena, 17.11.2020 в 11:51, в сообщении № 4 ()

Файл не прикрепился

Я кстати сейчас пробую. Все до банального просто и логично, но все равно ругается VBA. Я уже и так и так вертел и через переменную подставлял и без нее.

[vba]

Код

Private Sub CommandButton1_Click()
Dim bbbb As Variant

bbbb = "=" & Staff_list.Range(Staff_list.Cells(2, 1), Staff_list.Cells(9, 1)).Address

MsgBox bbbb
    With Cells(14, 3).Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=bbbb

    End With

End Sub

[/vba]

К сообщению приложен файл: 4031799.xlsm (23.5 Kb)

О_о ...и так можно было?

Ответить

Pelena

Дата: Вторник, 17.11.2020, 12:10 | Сообщение № 6

Группа: Админы

Ранг: Местный житель

Сообщений: 19404

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

У меня так сработало
[vba]

Код

Private Sub CommandButton1_Click()
Dim bbbb As Variant

bbbb = "=" & Staff_list.Name & "!" & Staff_list.Range(Staff_list.Cells(2, 1), Staff_list.Cells(9, 1)).Address

MsgBox bbbb
    With Cells(14, 3).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=bbbb

    End With

End Sub

[/vba]

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

lopuxi

Дата: Вторник, 17.11.2020, 12:31 | Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007, 2013

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

Я попробовал чистить ячейку. .Clear полностью все удаляет, с форматом. Это конечно не удобно... А .ClearContents не удаляет сам список из ячейки, а только очищает содержимое...

Цитата Pelena, 17.11.2020 в 12:10, в сообщении № 6 ()

У меня так сработало
Private Sub CommandButton1_Click()
Dim bbbb As Variant

bbbb = "=" & Staff_list.Name & "!" & Staff_list.Range(Staff_list.Cells(2, 1), Staff_list.Cells(9, 1)).Address

MsgBox bbbb
    With Cells(14, 3).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=bbbb

    End With

End Su

О_о ...и так можно было?

Ответить

lopuxi

Дата: Вторник, 17.11.2020, 12:44 | Сообщение № 8

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007, 2013

Ура!! Нашел как можно удалять выпадающий список. Теперь можно будет пустить цикл занесения и удаления выпадающего списка и таким образом каждый раз их обновлять :)))
Спасибо за помощь! :))

[vba]

Код

Private Sub CommandButton1_Click()
Dim bbbb As Variant

bbbb = "=" & Staff_list.Name & "!" & Staff_list.Range(Staff_list.Cells(2, 1), Staff_list.Cells(10, 1)).Address

MsgBox bbbb

    With Range(Cells(14, 3), Cells(27, 3)).Validation ' заносим во все ячейки выпадающий список
        .Delete ' удаляем предыдущий выпадающий список в ячейке.
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=bbbb ' вставляем обновленный выпадающий список с новыми или измененными значениями.
    End With

End Sub

[/vba]

О_о ...и так можно было?

Сообщение отредактировал lopuxi - Вторник, 17.11.2020, 12:58

Ответить

Pelena

Дата: Вторник, 17.11.2020, 15:08 | Сообщение № 9

Группа: Админы

Ранг: Местный житель

Сообщений: 19404

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

Собственно у меня в макросе эта строчка есть)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Выпадающий список в ячейках Excel, с изменяемым диапазоном. (Макросы/Sub)

Страница 1 из 1
1