Real-time USERFORM VBA Concat Index-Match

[herman925]

Hi guys,

I'm quite a newbie in VBA and I've been trying to create a userform for a function that Concatenates all the Index Match Values from a table of clients' names based on a single value.

Background:
1) All clients are assigned a Reference Number
2) If they're a family, they will have the same reference number,

thus the need for concatenate because a Ref. Number can be more than 1 name

Anyhow, I've been using this VBA to help me define the function:

Function LookUpConcat(ByVal SearchString As String, _
                      SearchRange As Range, _
                      ReturnRange As Range, _
                      Optional Delimiter As String = " ", _
                      Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, _
                      Optional MatchCase As Boolean = False)
                   
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
  
  If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
     (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    LookUpConcat = CVErr(xlErrRef)
  Else
    If Not MatchCase Then SearchString = UCase(SearchString)
    For X = 1 To SearchRange.Count
      If MatchCase Then
        CellVal = SearchRange(X).value
      Else
        CellVal = UCase(SearchRange(X).value)
      End If
      ReturnVal = ReturnRange(X).value
      If MatchWhole And CellVal = SearchString Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      End If
Continue:
    Next
    
    LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  End If
  
End Function

Before I begin creating a userform, I have a cell in a table that helps me populate the client's names. But it is getting to the point that an excel table to too long (too many rows and too many columns and I want a cleaner view, resulting in an exploration of USERFORM.

Generally, in an Excel table, I will assign a cell with the formula:

=IF(E2="","",LookUpConcat(E2,Table_MIS[CASENAME],Table_MIS[CLIENTNAME], ", "))

E2 = The cell where I input the Ref. Number
Table_MIS[CASENAME] = The Ref. Number column in a Masterfile I point to
Table_MIS[CLIENTNAME] = The client's name column in the Masterfile

This will then show all the clients' names separated by comma based on the Ref Number I input at column E

So, as you see, it is mostly an index match function, but that it has been supported by a Module.

I wonder if it is possible to incorporate this in VBA, as I'm creating a USERFORM


The form has the followings in concern:
1) A textbox called "REF_Input"
2) A label called "ClientName_Label"


I would like that, if a user types in the textbox REF_Input, the label caption of "ClientName_Label" automatically uses the formula specified above to show the results of the client's name.

I understand the Excel formulas but I don't for VBA for index match, so I'm looking to see how it would be possible to do that. Any help is appreciated. Thank you.

 

[herman925]

Bump/ Please help if any