Как я могу предотвратить отображение в поле со списком Excel повторяющихся записей и / или пробелов?

Я создал лист Excel (с именем «пользователи»), в котором есть список случайных имен в столбце A. Каждая ячейка в этом столбце связана с ячейкой проверки данных. Когда пользователь вводит символ в эту ячейку проверки данных и выбирает перетаскивание стрелка вниз, этот символ (или слово) передается в качестве аргумента функции поиска Excel для каждого имени. Если в результате поиска возвращается 1, это имя включается как часть динамического списка (столбец D), который в Turn отображается в ячейке проверки данных.Это можно увидеть ниже, где я ввел символ "A" и вернул список имен, которые все начинаются с этого символа.

Как я могу предотвратить отображение в поле со списком Excel повторяющихся записей и / или пробелов?

Я пошел дальше, создав отдельный лист (названный «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

Проблема, с которой я сталкиваюсь, заключается в том, что когда пользователь вводит в поле со списком и, следовательно, создает новый список имен на основе набранного символа (ов), поле со списком сохраняет исходную длину динамического списка - это не уменьшает размер списка, чтобы отразить уменьшенное количество имен, возвращенных в результате поиска. Кроме того, остаются пробелы и дублируются имена, чтобы «восполнить» эту остаточную длину. Пожалуйста, посмотрите изображение ниже, которое иллюстрирует это:

Как я могу предотвратить отображение в поле со списком Excel повторяющихся записей и / или пробелов?

Прошу прощения за длину и детализацию моего вопроса, но мне было интересно, как я могу предотвратить такое поведение поля со списком и позволить ему динамически уменьшать размер раскрывающегося списка, чтобы он соответствовал длине списка имен вернули?

Любые мысли / предложения приветствуются.

Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
0
0
343
1

Ответы 1

Этот фрагмент кода изменит размер раскрывающегося списка поля со списком в соответствии с размером динамического списка, созданного на листе «пользователи»:

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

Приведенный выше код выполняется каждый раз, когда пользователь вводит что-то в поле со списком для поиска имени.

Другие вопросы по теме