Я создал лист Excel (с именем «пользователи»), в котором есть список случайных имен в столбце A. Каждая ячейка в этом столбце связана с ячейкой проверки данных. Когда пользователь вводит символ в эту ячейку проверки данных и выбирает перетаскивание стрелка вниз, этот символ (или слово) передается в качестве аргумента функции поиска Excel для каждого имени. Если в результате поиска возвращается 1, это имя включается как часть динамического списка (столбец D), который в Turn отображается в ячейке проверки данных.Это можно увидеть ниже, где я ввел символ "A" и вернул список имен, которые все начинаются с этого символа.
Я пошел дальше, создав отдельный лист (названный «master»), на котором столбец A теперь содержит в каждой ячейке список проверки данных. Я использовал код VBA, так что, когда пользователь дважды щелкает одну из этих ячеек проверки данных, ее ссылка передается в качестве аргумента функции поиска, ранее использовавшейся в листе «пользователи». См. Ниже:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim SortCell As Variant
Dim master As Worksheet
Dim users As Worksheet
Set master = ThisWorkbook.Sheets("master")
Set users = ThisWorkbook.Sheets("users")
Set cboTemp = master.OLEObjects("DataCombo")
On Error Resume Next
On Error GoTo errHandler
'If found Data Validation cell
If Target.Validation.Type = 3 Then
'MsgBox Target.Address
SortCell = "master!" & Target.Address
'Set Target Address to that of search function in InCell column
With users
.Range("B2").Value = "=IF(IFERROR(SEARCH(" & SortCell & ",A2,1), 0)=1,1,0)"
.Range("B2:B131").FillDown
End With
End If
Я снова продвинулся вперед, в этом случае, когда пользователь дважды щелкает один из этих списков проверки данных в столбце A листа «master», появляется поле со списком с раскрывающимся списком всех случайных имен из «пользователей» простынь. Когда пользователь вводит текст в поле со списком, список имен фильтруется, чтобы отразить динамический список, созданный в столбце D «Пользователи». Полный код VBA для этого показан ниже:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim SortCell As Variant
Dim master As Worksheet
Dim users As Worksheet
Set master = ThisWorkbook.Sheets("master")
Set users = ThisWorkbook.Sheets("users")
Set cboTemp = master.OLEObjects("DataCombo")
On Error Resume Next
On Error GoTo errHandler
'If found Data Validation cell
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Cancel = True
Application.EnableEvents = False
'MsgBox Target.Address
SortCell = "master!" & Target.Address
'Set Target Address to that of search function in InCell column
With users
.Range("B2").Value = "=IF(IFERROR(SEARCH(" & SortCell & ",A2,1), 0)=1,1,0)"
.Range("B2:B131").FillDown
End With
'Get Data Validation Formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
'MsgBox str
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
Me.DataCombo.DropDown
End If
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
Проблема, с которой я сталкиваюсь, заключается в том, что когда пользователь вводит в поле со списком и, следовательно, создает новый список имен на основе набранного символа (ов), поле со списком сохраняет исходную длину динамического списка - это не уменьшает размер списка, чтобы отразить уменьшенное количество имен, возвращенных в результате поиска. Кроме того, остаются пробелы и дублируются имена, чтобы «восполнить» эту остаточную длину. Пожалуйста, посмотрите изображение ниже, которое иллюстрирует это:
Прошу прощения за длину и детализацию моего вопроса, но мне было интересно, как я могу предотвратить такое поведение поля со списком и позволить ему динамически уменьшать размер раскрывающегося списка, чтобы он соответствовал длине списка имен вернули?
Любые мысли / предложения приветствуются.
Этот фрагмент кода изменит размер раскрывающегося списка поля со списком в соответствии с размером динамического списка, созданного на листе «пользователи»:
Private Sub DataCombo_Change()
'When user types into the combo box, this will resize the dropdown list to match the length of the dynamic list created in "users"
With Me.DataCombo
.Visible = True
.ListFillRange = "Employees"
End With
End Sub
Приведенный выше код выполняется каждый раз, когда пользователь вводит что-то в поле со списком для поиска имени.