Dropdown Box Not Functioning

[dandy]

I have a tool that contains a list of all employees and their associated stamp number. Many of the employees have more than one stamp and so we type in multiple values in the cell.

Some people just enter the stamp number with several spaces in between but some people use the "ALT+ENTER" when entering stamps. The issue with the "ALT+ENTER" is that when I go to the dropdown box for that cell, the paragraph symbol is showing up in the selection.

Also, the tool does not let you just type in one of the stamps (assuming there is more than one in a cell) and search that way.

Any ideas? Let me know if you need more information.

Private Sub ComboBox23_DropButt*******()
    Values = ComboBox23.Value
    ComboBox23.Clear
    Call InitVars
    MatrixTab.Range("A4", MatrixTab.Cells(LastRow, LastColumn)).Sort key1:=MatrixTab.Range("D4:D" & LastRow), Order1:=xlAscending, Header:=xlNo
    For x = 4 To LastRow
        If ActiveSheet.Shapes("Check Box 4").ControlFormat.Value = 1 And ActiveSheet.Shapes("Check Box 5").ControlFormat.Value <> 1 Then
            If MatrixTab.Cells(x, 2).Value = "Y" Then
                ComboBox23.AddItem MatrixTab.Cells(x, 4).Value
                If MatrixTab.Cells(x, 5).Value <> "" Then
                    ComboBox23.AddItem MatrixTab.Cells(x, 5).Value
                Else
                End If
            Else
            End If
         ElseIf ActiveSheet.Shapes("Check Box 5").ControlFormat.Value = 1 And ActiveSheet.Shapes("Check Box 4").ControlFormat.Value <> 1 Then
            If MatrixTab.Cells(x, 2).Value = "N" Then
                ComboBox23.AddItem MatrixTab.Cells(x, 4).Value
                If MatrixTab.Cells(x, 5).Value <> "" Then
                    ComboBox23.AddItem MatrixTab.Cells(x, 5).Value
                Else
                End If
            Else
            End If
        ElseIf ActiveSheet.Shapes("Check Box 4").ControlFormat.Value = 1 And ActiveSheet.Shapes("Check Box 5").ControlFormat.Value = 1 Then
            ComboBox23.AddItem MatrixTab.Cells(x, 4).Value
            If MatrixTab.Cells(x, 5).Value <> "" Then
                ComboBox23.AddItem MatrixTab.Cells(x, 5).Value
            Else
            End If
        Else
            ComboBox23.AddItem MatrixTab.Cells(x, 4).Value
            If MatrixTab.Cells(x, 5).Value <> "" Then
                ComboBox23.AddItem MatrixTab.Cells(x, 5).Value
            Else
            End If
        End If
    Next
    ComboBox23.Value = Values
    Stamp = ComboBox23.Value
    
End Sub

 

[Yongle]

I have not attempted to do anything with your code - I simply looked at how to clean up your cell data and convert it into a combobox dropdown

Method 1 - Standardise the data BEFORE populating combobox
- take the values in column B
- replace Alt+Enter with a space (REPLACE function in VBA, SUBSTITUTE used in formula)
- remove leading, trailing & excess spaces (TRIM function)

Either by formula
in C2 copied down
=TRIM(SUBSTITUTE(B2, CHAR(10)," "))

Or by VBA
- places the "clean" values in column C

Sub CleanTheData()
    Dim rng As Range, cel As Range
    Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
    For Each cel In rng
            cel.Offset(, 1).Value = WorksheetFunction.Trim(Replace(cel.Value, Chr(10), " "))
    Next cel
End Sub

Populating Combobox dropdown
- cel is the range variable
- dropdown is created by splitting the cleaned cell value using a space as a delimiter

   Me.ComboBox1.List = Split([COLOR=#000080]cel[/COLOR].Value, " ")

Method 2 - Clean the data WHILST populating combobox
The VBA is exactly the same method except that it is dealt with in a single step

    Me.ComboBox1.List = Split(WorksheetFunction.Trim(Replace([COLOR=#000080]cel[/COLOR].Value, Chr(10), " ")), " ")

After Testing
I prefer standardised data because it is always easier to deal with.
To replace the data in column B with the "clean" data, using Sub CleanTheData ...

[B][I]instead of this...[/I][/B]
[COLOR=#000080]cel.Offset(, 1).Value[/COLOR] = WorksheetFunction.Trim(Replace(cel.Value, Chr(10), " "))
[B][I]use..[/I][/B]
[COLOR=#000080]cel.Value [/COLOR]= WorksheetFunction.Trim(Replace(cel.Value, Chr(10), " "))