Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Не выделять ячейки при создании выпадающего списка - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Не выделять ячейки при создании выпадающего списка (Макросы/Sub)
Не выделять ячейки при создании выпадающего списка
w00t Дата: Пятница, 06.05.2016, 11:30 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 127
Репутация: 3 ±
Замечаний: 0% ±

Книга и код

[vba]
Код

Sub DropDownList()
    Sheet1.Range("S6:X6").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='Sheet2'!$A$2:$A$341"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
[/vba]

Подскажите, пожалуйста:

* после выполнения кода хотелось бы сделать так, чтобы область со списком не выделялась (чтобы код выполнялся тихо, без визуальных "эффектов". Сейчас он выбирает диапазон и остается он выделенным. А делать смену фокуса вот этим[vba]
Код
Selection.Cells(1).Select
[/vba] не хочется);
* список выбора динамический (добавляю или удаляю элементы, периодически), как бы задать именно область из листа 2 начиная с A2 и до последней ячейки (список непрерывный).

Суть в том, чтобы на листе 1 в ячейках с S6:X6 и до последней заполненной строки вниз под этим диапазоном, всегда был актуальный список из листа 2 (с ячейки A2 и до последней заполненной ячейки вниз)
[moder]Дайте более конкретное название теме.[/moder]
К сообщению приложен файл: 3890136.xlsm(17Kb)


Сообщение отредактировал w00t - Пятница, 06.05.2016, 12:03
 
Ответить
СообщениеКнига и код

[vba]
Код

Sub DropDownList()
    Sheet1.Range("S6:X6").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='Sheet2'!$A$2:$A$341"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
[/vba]

Подскажите, пожалуйста:

* после выполнения кода хотелось бы сделать так, чтобы область со списком не выделялась (чтобы код выполнялся тихо, без визуальных "эффектов". Сейчас он выбирает диапазон и остается он выделенным. А делать смену фокуса вот этим[vba]
Код
Selection.Cells(1).Select
[/vba] не хочется);
* список выбора динамический (добавляю или удаляю элементы, периодически), как бы задать именно область из листа 2 начиная с A2 и до последней ячейки (список непрерывный).

Суть в том, чтобы на листе 1 в ячейках с S6:X6 и до последней заполненной строки вниз под этим диапазоном, всегда был актуальный список из листа 2 (с ячейки A2 и до последней заполненной ячейки вниз)
[moder]Дайте более конкретное название теме.[/moder]

Автор - w00t
Дата добавления - 06.05.2016 в 11:30
Manyasha Дата: Пятница, 06.05.2016, 11:53 | Сообщение № 2
Группа: Модераторы
Ранг: Старожил
Сообщений: 1956
Репутация: 815 ±
Замечаний: 0% ±

Excel 2010, 2016
w00t, для списка можно создать именованный диапазон:
Код
=Sheet2!$A$2:ИНДЕКС(Sheet2!$A:$A;ПОИСКПОЗ("яяя";Sheet2!$A:$A))

А из кода уберите все селекты:
[vba]
Код
Sub DropDownList()
    With Range(Sheet1.Range("S6:X6"), Sheet1.Range("S6:X6").End(xlDown)).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=list"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
[/vba]
[p.s.]А тему переназовите как-то так: "Не выделять ячейки при создании выпадающего списка". А то все равно не понятно, что конкретно нужно сделать.[/p.s.]
К сообщению приложен файл: 3890136-1.xlsm(18Kb)


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804


Сообщение отредактировал Manyasha - Пятница, 06.05.2016, 11:57
 
Ответить
Сообщениеw00t, для списка можно создать именованный диапазон:
Код
=Sheet2!$A$2:ИНДЕКС(Sheet2!$A:$A;ПОИСКПОЗ("яяя";Sheet2!$A:$A))

А из кода уберите все селекты:
[vba]
Код
Sub DropDownList()
    With Range(Sheet1.Range("S6:X6"), Sheet1.Range("S6:X6").End(xlDown)).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=list"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
[/vba]
[p.s.]А тему переназовите как-то так: "Не выделять ячейки при создании выпадающего списка". А то все равно не понятно, что конкретно нужно сделать.[/p.s.]

Автор - Manyasha
Дата добавления - 06.05.2016 в 11:53
Roman777 Дата: Пятница, 06.05.2016, 11:53 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 780
Репутация: 88 ±
Замечаний: 0% ±

Excel 2007, Excel 2013
w00t, мне кажется, всё-равно придётся сохранять текущие выделенные ячейки, а потом заного их выделять...
[vba]
Код
Sub DropDownList2()
    Dim rng As Range
    Set rng = Selection
    Sheet1.Range("S6:X6").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Sheet2!$A$2:$A$300"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    rng.Select
End Sub
[/vba]
А ну или да, как Manyasha говорит)))


Много чего не знаю!!!!

Сообщение отредактировал Roman777 - Пятница, 06.05.2016, 11:54
 
Ответить
Сообщениеw00t, мне кажется, всё-равно придётся сохранять текущие выделенные ячейки, а потом заного их выделять...
[vba]
Код
Sub DropDownList2()
    Dim rng As Range
    Set rng = Selection
    Sheet1.Range("S6:X6").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Sheet2!$A$2:$A$300"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    rng.Select
End Sub
[/vba]
А ну или да, как Manyasha говорит)))

Автор - Roman777
Дата добавления - 06.05.2016 в 11:53
devilkurs Дата: Пятница, 06.05.2016, 12:08 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 167
Репутация: 43 ±
Замечаний: 0% ±

Excel 2007, 2010
Мой вариант

[vba]
Код
Sub DropDownList()
    Set R = Sheet1.Range("S6:X6")
    With Sheet1.Range(R, R.End(xlDown)).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='Sheet2'!$A$2:$A$" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
[/vba]
Пока добрался уже столько ответов. ))))))




Сообщение отредактировал devilkurs - Пятница, 06.05.2016, 12:10
 
Ответить
СообщениеМой вариант

[vba]
Код
Sub DropDownList()
    Set R = Sheet1.Range("S6:X6")
    With Sheet1.Range(R, R.End(xlDown)).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='Sheet2'!$A$2:$A$" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
[/vba]
Пока добрался уже столько ответов. ))))))

Автор - devilkurs
Дата добавления - 06.05.2016 в 12:08
w00t Дата: Пятница, 06.05.2016, 12:22 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 127
Репутация: 3 ±
Замечаний: 0% ±

Пока добрался уже столько ответов. ))))))


Да, но это и хорошо, спасибо всем большое!
 
Ответить
Сообщение
Пока добрался уже столько ответов. ))))))


Да, но это и хорошо, спасибо всем большое!

Автор - w00t
Дата добавления - 06.05.2016 в 12:22
w00t Дата: Пятница, 06.05.2016, 12:23 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 127
Репутация: 3 ±
Замечаний: 0% ±

Пока добрался уже столько ответов. ))))))


Да, но это и хорошо, спасибо всем большое!
 
Ответить
Сообщение
Пока добрался уже столько ответов. ))))))


Да, но это и хорошо, спасибо всем большое!

Автор - w00t
Дата добавления - 06.05.2016 в 12:23
_Boroda_ Дата: Пятница, 06.05.2016, 12:28 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11269
Репутация: 4656 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
1. Если ниже строки 6 ничего нет, то, возможно, не стоит засовывать проверку в (1 048 576 - 5) * 6 = 6 291 426 ячеек? Положим ее только в строку 6.
2. Имя для диапазона я написал вот так:
Код
=Sheet2!$A$2:ИНДЕКС(Sheet2!$A:$A;СЧЁТЗ(Sheet2!$A:$A))

3. End(xlDown) найдет ячейку перед ПЕРВОЙ незаполненной, а вот End(xlUp) найдет как раз ПОСЛЕДНЮЮ заполненную
4. ...Range("S6:X6").End(xlDown)).Row будет искать только по первому столбцу диапазона - S. А если последняя заполненная не в S, а в W, например?
Я бы вот так сделал:
[vba]
Код
Sub DropDownList()
    r_ = 6
    For i = 19 To 24
        r1_ = Cells(Rows.Count, i).End(xlUp).Row
        If r1_ > r_ Then r_ = r1_
    Next i
    With Range("S6:X" & r_).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=спис"
    End With
End Sub
[/vba]
К сообщению приложен файл: 3890136_1.xlsm(18Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение1. Если ниже строки 6 ничего нет, то, возможно, не стоит засовывать проверку в (1 048 576 - 5) * 6 = 6 291 426 ячеек? Положим ее только в строку 6.
2. Имя для диапазона я написал вот так:
Код
=Sheet2!$A$2:ИНДЕКС(Sheet2!$A:$A;СЧЁТЗ(Sheet2!$A:$A))

3. End(xlDown) найдет ячейку перед ПЕРВОЙ незаполненной, а вот End(xlUp) найдет как раз ПОСЛЕДНЮЮ заполненную
4. ...Range("S6:X6").End(xlDown)).Row будет искать только по первому столбцу диапазона - S. А если последняя заполненная не в S, а в W, например?
Я бы вот так сделал:
[vba]
Код
Sub DropDownList()
    r_ = 6
    For i = 19 To 24
        r1_ = Cells(Rows.Count, i).End(xlUp).Row
        If r1_ > r_ Then r_ = r1_
    Next i
    With Range("S6:X" & r_).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=спис"
    End With
End Sub
[/vba]

Автор - _Boroda_
Дата добавления - 06.05.2016 в 12:28
w00t Дата: Пятница, 06.05.2016, 12:59 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 127
Репутация: 3 ±
Замечаний: 0% ±

For i = 19 To 24

Спасибо. Пытаюсь понять вот это..
[moder]Номера столбцов S-X
 
Ответить
Сообщение
For i = 19 To 24

Спасибо. Пытаюсь понять вот это..
[moder]Номера столбцов S-X

Автор - w00t
Дата добавления - 06.05.2016 в 12:59
w00t Дата: Среда, 25.05.2016, 09:30 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 127
Репутация: 3 ±
Замечаний: 0% ±

Имя для диапазона я написал вот так:

=Sheet2!$A$2:ИНДЕКС(Sheet2!$A:$A;СЧЁТЗ(Sheet2!$A:$A))


Насчет предварительной сортировки - сделал в VBA, подумал, что так будет лучше, чем делать в формуле

[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tValue As Variant, rOffset As Long, cOffset As Long
    
    With Target
        If .Column = 1 Then
            tValue = .Cells(1, 1).Value
            rOffset = ActiveCell.Row - .Row
            cOffset = ActiveCell.Column - .Column
            
            With Columns(1)
                With Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).EntireRow
                    .Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlNo
                End With
                With .Find(tValue, after:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
                    Application.Goto .Cells
                    If .Row = 2 And rOffset = -1 Then rOffset = 0
                    .Offset(rOffset, cOffset).Select
                End With
            End With
        End If
    End With
    Application.EnableEvents = True
End Sub
[/vba]

Я бы вот так сделал:
Sub DropDownList()
r_ = 6
For i = 19 To 24
r1_ = Cells(Rows.Count, i).End(xlUp).Row
If r1_ > r_ Then r_ = r1_
Next i
With Range("S6:X" & r_).Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=спис"
End With
End Sub


по этой части, если будет возможность кому-то меня сориентировать, - как найти последнюю строку, ориентируясь на заливку ячеек. Все то же самое (но достаточно будет, если Range ("F6:F") имеет заливку строк). То выпадающий список на S6:X и до самой последней строки с заливкой (ту, которую по F6:F найдем?).

Вообще, начиная с 6 строки, непрерывная заливка на большой диапазон, просто по столбу F с 6 строки и по конец залитых цветом найти бы границы (залитых цветом, любым). И на соответствующий диапазон S6:X - выпадающий?
[moder]Хоть я и мало что понял из этого поста, но почему-то кажется, что это тянет на новый вопрос, а не на продолжение текущего[/moder]


Сообщение отредактировал _Boroda_ - Среда, 25.05.2016, 09:53
 
Ответить
Сообщение
Имя для диапазона я написал вот так:

=Sheet2!$A$2:ИНДЕКС(Sheet2!$A:$A;СЧЁТЗ(Sheet2!$A:$A))


Насчет предварительной сортировки - сделал в VBA, подумал, что так будет лучше, чем делать в формуле

[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tValue As Variant, rOffset As Long, cOffset As Long
    
    With Target
        If .Column = 1 Then
            tValue = .Cells(1, 1).Value
            rOffset = ActiveCell.Row - .Row
            cOffset = ActiveCell.Column - .Column
            
            With Columns(1)
                With Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).EntireRow
                    .Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlNo
                End With
                With .Find(tValue, after:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
                    Application.Goto .Cells
                    If .Row = 2 And rOffset = -1 Then rOffset = 0
                    .Offset(rOffset, cOffset).Select
                End With
            End With
        End If
    End With
    Application.EnableEvents = True
End Sub
[/vba]

Я бы вот так сделал:
Sub DropDownList()
r_ = 6
For i = 19 To 24
r1_ = Cells(Rows.Count, i).End(xlUp).Row
If r1_ > r_ Then r_ = r1_
Next i
With Range("S6:X" & r_).Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=спис"
End With
End Sub


по этой части, если будет возможность кому-то меня сориентировать, - как найти последнюю строку, ориентируясь на заливку ячеек. Все то же самое (но достаточно будет, если Range ("F6:F") имеет заливку строк). То выпадающий список на S6:X и до самой последней строки с заливкой (ту, которую по F6:F найдем?).

Вообще, начиная с 6 строки, непрерывная заливка на большой диапазон, просто по столбу F с 6 строки и по конец залитых цветом найти бы границы (залитых цветом, любым). И на соответствующий диапазон S6:X - выпадающий?
[moder]Хоть я и мало что понял из этого поста, но почему-то кажется, что это тянет на новый вопрос, а не на продолжение текущего[/moder]

Автор - w00t
Дата добавления - 25.05.2016 в 09:30
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Не выделять ячейки при создании выпадающего списка (Макросы/Sub)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!