Handling name variants
First, I thought I'd mention that your code is stylistically really good - variable and function names make sense, indentation is great (with one small exception noted below), and the functions are focused and readable.  Just one minor point:

The code under each Case in a Select Case is typically indented one more level:

Select Case adviser
    Case JonHussey
        AddNameVariations coll, "Jon", "Hussey"
    Case MartinCotter
        '...


As far as your specific question goes, this actually looks like it would be quite a chore to maintain.  Adding a new name to an existing advisor is easy with your set-up.  For example, if John Cusins decided to change his name to Jim Nasium, all you would have to do is add AddNameVariations coll, "Jim", "Nasium" under his Case in NameStringsFromAdviser and change his Case in CurrentNameStringFromAdviser to NameString = "Nasium, Jim".

But, this points to the next issue - your Enum isn't really serving much use as much other than a place-holder.  On the one hand you could change the JohnCusins element to JimNasium everywhere in your code, but this kind of defeats the purpose of having a named identifier.  On the other hand you could leave it as JohnCusins, but this now means that it isn't appropriately named - why should the Enum element associated with a person be different from their name? If I was only looking at the CurrentNameStringFromAdviser function and not the entire module, I'd be wondering "why is the SarahCotter case returning Oluwole, Sarah?"

This also points to the main issue in maintainability and extensibility - to add an advisor, you need to change the code in 3 different functions and add a value to the enumeration.  At the same time, you need to make sure that those changes are all consistent with each other. The urge to copy and paste will be very hard to resist...

...which leads to the last point - you're repeating code all over the place. This gives me a whiff of code smell. Wouldn't it be nice if you didn't have to type If AdviserNameInString(FooBar, str) Then again and again and again?  It would be much better to approach this problem with the mantra Don't Repeat Yourself in mind.  This is a problem begging for an OO solution.

You have 2 distinct things you care about - advisors and names. So make an advisor class and a name class.  Make them responsible for the things that advisors and names care about.  I.e.:

Name.cls

Option Explicit

Private mFirst As String
Private mLast As String

Public Property Let FirstName(first As String)
    mFirst = first
End Property

Public Property Get FirstName() As String
    FirstName = mFirst
End Property

Public Property Let LastName(last As String)
    mLast = last
End Property

Public Property Get LastName() As String
    LastName = mLast
End Property

Public Property Get NameString() As String
    NameString = mLast &amp; ", " &amp; mFirst
End Property

Public Function Matches(test As String) As Boolean
    Matches = InStr(1, test, mFirst) Or InStr(1, test, mLast)
End Function


Advisor.cls:

Option Explicit

Private mNames As New Collection
Private mPreferred As Name

Public Sub AddName(inValue As Name)
    mNames.Add inValue
End Sub

Public Property Set PreferredName(inValue As Name)
    Set mPreferred = inValue
    mNames.Add inValue
End Property

Public Property Get PreferredName() As Name
    Set PreferredName = mPreferred
End Property

Public Function NameMatches(test As String) As Boolean
    Dim candidate As Variant
    For Each candidate In mNames
        If candidate.Matches(test) Then
            NameMatches = True
            Exit For
        End If
    Next candidate
End Function


Note, it isn't clear from your question if the numbers in the Enum have meaning outside of the code that you posted. If they are, just make them a property of Advisor. Throw in a couple factory functions to easily generate objects (curse you VBA for not having constructors)...

'Name factory.
Public Function NewName(first As String, last As String) As Name
    Set NewName = New Name
    NewName.FirstName = first
    NewName.LastName = last
End Function

'Advisor factory
Public Function NewAdvisor(preferred As Name, _
                           ParamArray names() As Variant) As Advisor
    Dim variation As Variant

    Set NewAdvisor = New Advisor
    Set NewAdvisor.PreferredName = preferred

    For Each variation In names
        Dim cast As Name
        Set cast = variation
        NewAdvisor.AddName cast
    Next variation
End Function


...and you get something to maintain that looks like this:

Option Explicit

Private Advisors As Collection

Public Sub InitializeAdvisors()
    Set Advisors = New Collection

    Advisors.Add NewAdvisor(NewName("Jon", "Hussey"))
    Advisors.Add NewAdvisor(NewName("Martin", "Cotter"))
    Advisors.Add NewAdvisor(NewName("Jeremy", "Smith"))
    Advisors.Add NewAdvisor(NewName("Jonathan", "Blair"))
    Advisors.Add NewAdvisor(NewName("Sarah", "Oluwole"), _
                            NewName("Sarah", "Cotter"))
    Advisors.Add NewAdvisor(NewName("John", "Cusins"))
    Advisors.Add NewAdvisor(NewName("Micky", "Mahbubani"))
End Sub

Public Function FindAdvisor(inValue As String) As Advisor
    Dim candidate As Variant
    For Each candidate In Advisors
        If candidate.NameMatches(inValue) Then
            Set FindAdvisor = candidate
            Exit Function
        End If
    Next candidate
End Function


Example usage:

Private Sub ExampleUsage()
    InitializeAdvisors
    Debug.Print FindAdvisor("Someone named Sarah").PreferredName().NameString
End Sub


Add an advisor? One line of code. Add an alias? Change one line of code. Remove an advisor? Delete one line of code. Change from "Last, First" format to "First Last" format? Change one line of code. An advisor has 42 aliases? No problem - just add them all in one place. Now that is easy to maintain.