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

Вход

Регистрация

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

 

= Мир MS Excel/Как обратиться к именованному диапазону - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Как обратиться к именованному диапазону (Макросы/Sub)
Как обратиться к именованному диапазону
skais Дата: Понедельник, 05.03.2018, 11:15 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 86
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
На листе есть combobox Activex - каким образом загрузить в список из именованного диапазона к примеру Сотрудники?

[vba]
Код

With Sheets(1)
        'ls = Сотрудники
        ls = .Range(.Cells(1, k), .Cells(Rows.Count, k).End(xlUp))
'        ls = [Сотрудники].Address
    End With
    Application.EnableEvents = False
    With Me.ComboBox1
        .Activate
        .Top = at.Top
        .Left = at.Left
        .Width = at.Width + 16
        .Height = at.Height
        .Clear
        '.RowSource = Range("B1:B12")
        .List = unicList(ls)
        .Value = at.Value
        .Font.Size = Round(.Height / 2)
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
    Application.EnableEvents = True
    Return
[/vba]
 
Ответить
СообщениеНа листе есть combobox Activex - каким образом загрузить в список из именованного диапазона к примеру Сотрудники?

[vba]
Код

With Sheets(1)
        'ls = Сотрудники
        ls = .Range(.Cells(1, k), .Cells(Rows.Count, k).End(xlUp))
'        ls = [Сотрудники].Address
    End With
    Application.EnableEvents = False
    With Me.ComboBox1
        .Activate
        .Top = at.Top
        .Left = at.Left
        .Width = at.Width + 16
        .Height = at.Height
        .Clear
        '.RowSource = Range("B1:B12")
        .List = unicList(ls)
        .Value = at.Value
        .Font.Size = Round(.Height / 2)
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
    Application.EnableEvents = True
    Return
[/vba]

Автор - skais
Дата добавления - 05.03.2018 в 11:15
and_evg Дата: Понедельник, 05.03.2018, 11:31 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 53 ±
Замечаний: 0% ±

Excel 2007
На листе есть

А у нас даже и листа нет!!!
 
Ответить
Сообщение
На листе есть

А у нас даже и листа нет!!!

Автор - and_evg
Дата добавления - 05.03.2018 в 11:31
Manyasha Дата: Понедельник, 05.03.2018, 11:38 | Сообщение № 3
Группа: Модераторы
Ранг: Старожил
Сообщений: 2164
Репутация: 878 ±
Замечаний: 0% ±

Excel 2010, 2016
Без файла не понятно, что такое unicList, но сам именованный диапазон можно так
[vba]
Код
.ListFillRange = [Сотрудники].Address
[/vba]


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеБез файла не понятно, что такое unicList, но сам именованный диапазон можно так
[vba]
Код
.ListFillRange = [Сотрудники].Address
[/vba]

Автор - Manyasha
Дата добавления - 05.03.2018 в 11:38
skais Дата: Понедельник, 05.03.2018, 11:49 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 86
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
Вот весь код, однако не получается [vba]
Код
= [Сотрудники].Address
[/vba]

[vba]
Код
Dim at As Range
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("C26")) Is Nothing And Target.Count = 1 Then
        With UserForm2
             .Show 0
             .Top = .Top - 150
             .ComboBox1.RowSource = "Списки!N1"
        End With
        Cancel = True
    End If
If Not Intersect(Target, Range("C27")) Is Nothing And Target.Count = 1 Then
        With UserForm2
             .Show 0
             .Top = .Top - 150
             .ComboBox1.RowSource = "Списки!P1"
        End With
        Cancel = True
    End If
End Sub
Private Sub ComboBox1_Change()
Dim lstVal
    Application.EnableEvents = False
    For Each lstVal In Me.ComboBox1.List
        If CStr(lstVal) = Me.ComboBox1 Then
            Exit For
        Else: lstVal = ""
        End If
    Next
    at.Value = lstVal
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("C13,D13,C49")) Is Nothing Then UserForm1.Show

    Dim ls
    Set at = Target(1)
    Select Case at.Column
    Case 5: k = 1: GoSub FillComb
    Case 7: k = 3: GoSub FillComb
    Case 9: k = 5: GoSub FillComb
    Case Else: HideCombo
    End Select
    Exit Sub
FillComb:
    With Sheets(1)
        'ls = Сотрудники
        ls = .Range(.Cells(1, k), .Cells(Rows.Count, k).End(xlUp))
        'ls = [Сотрудники].Address
    End With
    Application.EnableEvents = False
    With Me.ComboBox1
        .Activate
        .Top = at.Top
        .Left = at.Left
        .Width = at.Width + 16
        .Height = at.Height
        .Clear
        '.RowSource = Range("B1:B12")
        .List = unicList(ls)
        .Value = at.Value
        .Font.Size = Round(.Height / 2)
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
    Application.EnableEvents = True
    Return
End Sub
Private Sub HideCombo()
    Me.ComboBox1.Top = 0
    Me.ComboBox1.Left = 0
    Me.ComboBox1.Width = 0
    Me.ComboBox1.Height = 0
End Sub

Private Function unicList(ls)
    Dim lst, i&
    If IsObject(ls) Then ls = ls.Value
    ls = IIf(IsArray(ls), ls, Array(ls))
    On Error Resume Next
    With New Collection
        For Each lst In ls
            If Len(lst) Then
                For i = 1 To .Count
                    If lst < .Item(i) Then .Add lst, CStr(lst), before:=i: Exit For
                Next
                .Add lst, CStr(lst)
            End If
        Next
        ReDim ls(1 To .Count)
        For i = 1 To .Count: ls(i) = .Item(i): Next
        unicList = ls
    End With
End Function
[/vba]


Сообщение отредактировал skais - Понедельник, 05.03.2018, 11:49
 
Ответить
СообщениеВот весь код, однако не получается [vba]
Код
= [Сотрудники].Address
[/vba]

[vba]
Код
Dim at As Range
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("C26")) Is Nothing And Target.Count = 1 Then
        With UserForm2
             .Show 0
             .Top = .Top - 150
             .ComboBox1.RowSource = "Списки!N1"
        End With
        Cancel = True
    End If
If Not Intersect(Target, Range("C27")) Is Nothing And Target.Count = 1 Then
        With UserForm2
             .Show 0
             .Top = .Top - 150
             .ComboBox1.RowSource = "Списки!P1"
        End With
        Cancel = True
    End If
End Sub
Private Sub ComboBox1_Change()
Dim lstVal
    Application.EnableEvents = False
    For Each lstVal In Me.ComboBox1.List
        If CStr(lstVal) = Me.ComboBox1 Then
            Exit For
        Else: lstVal = ""
        End If
    Next
    at.Value = lstVal
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("C13,D13,C49")) Is Nothing Then UserForm1.Show

    Dim ls
    Set at = Target(1)
    Select Case at.Column
    Case 5: k = 1: GoSub FillComb
    Case 7: k = 3: GoSub FillComb
    Case 9: k = 5: GoSub FillComb
    Case Else: HideCombo
    End Select
    Exit Sub
FillComb:
    With Sheets(1)
        'ls = Сотрудники
        ls = .Range(.Cells(1, k), .Cells(Rows.Count, k).End(xlUp))
        'ls = [Сотрудники].Address
    End With
    Application.EnableEvents = False
    With Me.ComboBox1
        .Activate
        .Top = at.Top
        .Left = at.Left
        .Width = at.Width + 16
        .Height = at.Height
        .Clear
        '.RowSource = Range("B1:B12")
        .List = unicList(ls)
        .Value = at.Value
        .Font.Size = Round(.Height / 2)
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
    Application.EnableEvents = True
    Return
End Sub
Private Sub HideCombo()
    Me.ComboBox1.Top = 0
    Me.ComboBox1.Left = 0
    Me.ComboBox1.Width = 0
    Me.ComboBox1.Height = 0
End Sub

Private Function unicList(ls)
    Dim lst, i&
    If IsObject(ls) Then ls = ls.Value
    ls = IIf(IsArray(ls), ls, Array(ls))
    On Error Resume Next
    With New Collection
        For Each lst In ls
            If Len(lst) Then
                For i = 1 To .Count
                    If lst < .Item(i) Then .Add lst, CStr(lst), before:=i: Exit For
                Next
                .Add lst, CStr(lst)
            End If
        Next
        ReDim ls(1 To .Count)
        For i = 1 To .Count: ls(i) = .Item(i): Next
        unicList = ls
    End With
End Function
[/vba]

Автор - skais
Дата добавления - 05.03.2018 в 11:49
Manyasha Дата: Понедельник, 05.03.2018, 11:51 | Сообщение № 5
Группа: Модераторы
Ранг: Старожил
Сообщений: 2164
Репутация: 878 ±
Замечаний: 0% ±

Excel 2010, 2016
Андрей (and_evg) Вам уже намекнул, что нужен файл.


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеАндрей (and_evg) Вам уже намекнул, что нужен файл.

Автор - Manyasha
Дата добавления - 05.03.2018 в 11:51
skais Дата: Понедельник, 05.03.2018, 12:02 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 86
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
Пример не мой но смысл тот
Нужно в список загнать данные из именованного диапазона.
И еще вопрос почему если этот же combobox на форме то у него есть свойство rowsource, а если на листе его нет?
К сообщению приложен файл: 7581352.xls(45.0 Kb)
 
Ответить
СообщениеПример не мой но смысл тот
Нужно в список загнать данные из именованного диапазона.
И еще вопрос почему если этот же combobox на форме то у него есть свойство rowsource, а если на листе его нет?

Автор - skais
Дата добавления - 05.03.2018 в 12:02
sboy Дата: Понедельник, 05.03.2018, 16:18 | Сообщение № 7
Группа: Проверенные
Ранг: Старожил
Сообщений: 1872
Репутация: 552 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
[vba]
Код
ls = Range(ActiveWorkbook.Names("Сотрудники").RefersToRange.Address).Value
[/vba]
 
Ответить
СообщениеДобрый день.
[vba]
Код
ls = Range(ActiveWorkbook.Names("Сотрудники").RefersToRange.Address).Value
[/vba]

Автор - sboy
Дата добавления - 05.03.2018 в 16:18
skais Дата: Понедельник, 05.03.2018, 17:27 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 86
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
Manyasha,sboy - спасибо помогли!
Однако у меня проблема - при указании диапазона через формулу смещ (для зависимых списков), оно воспринимает как пустое, хотя сам диапазон рабочий если использовать обычный выпадающий список или через combobox на userform.
Посмотрите пожалуйста, в чем причина?
К сообщению приложен файл: gggg.xls(41.5 Kb)


Сообщение отредактировал skais - Понедельник, 05.03.2018, 17:29
 
Ответить
СообщениеManyasha,sboy - спасибо помогли!
Однако у меня проблема - при указании диапазона через формулу смещ (для зависимых списков), оно воспринимает как пустое, хотя сам диапазон рабочий если использовать обычный выпадающий список или через combobox на userform.
Посмотрите пожалуйста, в чем причина?

Автор - skais
Дата добавления - 05.03.2018 в 17:27
sboy Дата: Понедельник, 05.03.2018, 17:49 | Сообщение № 9
Группа: Проверенные
Ранг: Старожил
Сообщений: 1872
Репутация: 552 ±
Замечаний: 0% ±

Excel 2010
Попробуйте так
[vba]
Код
Set ls =ActiveWorkbook.Names("Сотрудники").RefersToRange
[/vba]
на файле не проверял
 
Ответить
СообщениеПопробуйте так
[vba]
Код
Set ls =ActiveWorkbook.Names("Сотрудники").RefersToRange
[/vba]
на файле не проверял

Автор - sboy
Дата добавления - 05.03.2018 в 17:49
skais Дата: Понедельник, 05.03.2018, 17:56 | Сообщение № 10
Группа: Пользователи
Ранг: Участник
Сообщений: 86
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
sboy не работает,не могли бы Вы в самом файле посмотреть, обычный диапазон он воспринимает по Вашему первому примеру, а вот со смещ нет!
Combo в E7 Зависит от D7


Сообщение отредактировал skais - Понедельник, 05.03.2018, 17:57
 
Ответить
Сообщениеsboy не работает,не могли бы Вы в самом файле посмотреть, обычный диапазон он воспринимает по Вашему первому примеру, а вот со смещ нет!
Combo в E7 Зависит от D7

Автор - skais
Дата добавления - 05.03.2018 в 17:56
Karataev Дата: Вторник, 06.03.2018, 09:23 | Сообщение № 11
Группа: Проверенные
Ранг: Старожил
Сообщений: 1236
Репутация: 470 ±
Замечаний: 0% ±

Excel
skais, для Вашей задачи не подходит использование имени. Вам нужно отказаться от использования имени и использовать что-нибудь другое.

Цитата из справки:
RefersToRange
If the Name object doesn't refer to a range (for example, if it refers to a constant or a formula), this property fails.

Что означает, что если в имени используется формула (это в Вашем случае), то свойство fails, т.е. не работает.


Киви-кошелек: 9166309108
Яндекс-деньги: 410014131888288
 
Ответить
Сообщениеskais, для Вашей задачи не подходит использование имени. Вам нужно отказаться от использования имени и использовать что-нибудь другое.

Цитата из справки:
RefersToRange
If the Name object doesn't refer to a range (for example, if it refers to a constant or a formula), this property fails.

Что означает, что если в имени используется формула (это в Вашем случае), то свойство fails, т.е. не работает.

Автор - Karataev
Дата добавления - 06.03.2018 в 09:23
skais Дата: Вторник, 06.03.2018, 11:34 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 86
Репутация: 0 ±
Замечаний: 60% ±

Excel 2010
Karataev Позвольте не согласиться с Вами, потому как я использую тоже формулу смещ(немного другую) и она работает с listFillRange
эта работает
[vba]
Код
=OFFSET(Списки!$C$2;0;0;COUNTA(Списки!$C:$C)-1;1)
[/vba]
а эта не работает
[vba]
Код
=OFFSET(Списки!$I$1;MATCH(Пульт!$C17;Списки!$I:$I;0)-1;1;COUNTIF(Списки!$I:$I;Пульт!$C17);1)
[/vba]

В чем разница?


Сообщение отредактировал skais - Вторник, 06.03.2018, 11:36
 
Ответить
СообщениеKarataev Позвольте не согласиться с Вами, потому как я использую тоже формулу смещ(немного другую) и она работает с listFillRange
эта работает
[vba]
Код
=OFFSET(Списки!$C$2;0;0;COUNTA(Списки!$C:$C)-1;1)
[/vba]
а эта не работает
[vba]
Код
=OFFSET(Списки!$I$1;MATCH(Пульт!$C17;Списки!$I:$I;0)-1;1;COUNTIF(Списки!$I:$I;Пульт!$C17);1)
[/vba]

В чем разница?

Автор - skais
Дата добавления - 06.03.2018 в 11:34
Karataev Дата: Четверг, 08.03.2018, 20:04 | Сообщение № 13
Группа: Проверенные
Ранг: Старожил
Сообщений: 1236
Репутация: 470 ±
Замечаний: 0% ±

Excel
Вот так можно заполнить ComboBox:
[vba]
Код
ls = [Сотрудники].Value
[/vba]


Киви-кошелек: 9166309108
Яндекс-деньги: 410014131888288
 
Ответить
СообщениеВот так можно заполнить ComboBox:
[vba]
Код
ls = [Сотрудники].Value
[/vba]

Автор - Karataev
Дата добавления - 08.03.2018 в 20:04
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Как обратиться к именованному диапазону (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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