Move the focus to a different control on a userform

If you use the Change Event rather than AfterUpdate then it will work provided the user selects a value using the DropDown.

Caveat: If the user commences typing in the combobox then the Change event fires with the first character typed.

Private Sub cmbx_siteName_Change()

    If cmbx_siteName.Value <> "" Then

        Me.txtbx_siteID.Value = WorksheetFunction.VLookup(Me.cmbx_siteName.Value, Sheet5.Range("I:J"), 2, 0)

        Me.txtbx_siteID.SetFocus

    End If

End Sub

"Caveat: If the user commences typing in the combobox then the Change event fires with the first character typed."  This is the situation I have currently. These are all my Subs:

Private Sub cmbx_siteName_AfterUpdate()

    If cmbx_siteName.Value <> "" Then

        Me.txtbx_siteID.Value = WorksheetFunction.VLookup(Me.cmbx_siteName.Value, Sheet5.Range("I:J"), 2, 0)

        Me.txtbx_siteID.SetFocus

    End If

End Sub

Private Sub cmbx_SiteName_Change()

 Dim strSelected As String

    strSelected = Me.cmbx_siteName.Value

    If strSelected = "" Then

        Me.cmbx_siteName.List = varArr

    Else

        Me.cmbx_siteName.List = SearchAndAppend(strSelected)

    End If

    Me.cmbx_siteName.DropDown

End Sub

Private Sub cmdClose_Click()

    Unload Me

End Sub

Private Sub cmbx_SiteName_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    Me.cmbx_siteName.List = varArr

End Sub

Private Sub UserForm_Initialize()

    '-- Assing your list to array here.

    varArr = Intersect(Sheet5.Range("I1").CurrentRegion, Sheet5.Range("I1").CurrentRegion.Offset(1)).Columns(1)

    Me.cmbx_siteName.List = varArr

End Sub

Function SearchAndAppend(strSubString As String)

Dim lngR As Long

Dim varResult

    For lngR = LBound(varArr) To UBound(varArr)

        If varArr(lngR, 1) Like "*" & strSubString & "*" Then

            If Not IsArray(varResult) Then

                ReDim varResult(0)

            Else

                ReDim Preserve varResult(UBound(varResult) + 1)

            End If

            varResult(UBound(varResult)) = varArr(lngR, 1)

        End If

    Next lngR

    If Not IsArray(varResult) Then

        ReDim varResult(0)

        varResult(0) = "No Match"

    End If

    SearchAndAppend = varResult

End Function

I can't follow what you are attempting to do.

The Change event fires every time a character is typed. Initially I thought that you were trying to add an additional choice to the list if the user typed in something different to what is included in the list. However, because the Change code fires every time a character is typed I can't see how that can occur.

Please explain exactly what you are attempting to achieve with the code.

I have created an example workbook by adapting some code that I have to try to emulate what it appears to me that you are attempting to do. ie Auto select the next required control after making a ComboBox selection and also allowing the User to enter a new name instead of selecting from the existing Combo Dropdown and then save the new name.

If the User selects an existing item then the Focus is moved to the next required control ie. ID.

If the User types a new name then it is necessary to press Enter or Tab or use the Mouse to select another control. I don't know of a way to avoid this because otherwise how will the system know when the User has finished typing the new name.

If an existing name is selected then the ID is automatically looked up and the focus is set to the ID. If a new name is typed then after pressing Enter or Tab then the User gets a MsgBox to confirm to add the name to the list. I assume that you will be adding code for the User to enter the correct ID if a new name is added and then you will look up the name and insert the ID beside it.

The following link is to a Zipped file uploaded to OneDrive.

Download and extract and test and see if it does what you require. I zip the files so they do not auto open with OnLine Excel when in OneDrive because the OnLine version of Excel lacks the necessary functionality to run the macro enabled workbook.

https://1drv.ms/u/s!ArAXPS2RpafCtDwHFDqjHVy2R6NO?e=bYcUZK 

Feel free to get back to me if any problems with it not performing as you required.

Thanks for all the help Mac. The file you sent is about what I am looking for. But I have shared the link to the file I am working on. Maybe you can tell me what I am doing wrong. Also you will find that when you select a name in the combobox drop down, the name that is displayed is not the one you selected. It started doing that a few days ago, out of the blue. Even if you type in a few letters into the combobox you get the same odd

https://1drv.ms/x/s!AvRiDKlTKUp0nkH_AAzlhY9RRCPo?e=xEo0RJ

Please upload the file to OneDrive as a Zipped file. When I select the link the workbook is auto opening with Excel On-line and when I try to open it with my Excel app I am losing some functionality of the workbook so I can't do anything with it. 

I mentioned this in my previous post "I zip the files so they do not auto open with OnLine Excel when in OneDrive because the OnLine version of Excel lacks the necessary functionality to run the macro enabled workbook."

To Zip a file select the file in Windows Explorer and Right click -> Send to -> Compressed (Zipped) folder.

Your comment:  "Also you will find that when you select a name in the combobox drop down, the name that is displayed is not the one you selected. It started doing that a few days ago, out of the blue. Even if you type in a few letters into the combobox you get the same odd"

I thought that the following might help without me getting a copy of your workbook.

Could be due to the ComboBox MatchEntry property. Check that the ComboBox MatchEntry property is set to the following.

     2 - fmMatchEntryNone

This is to suppress the system from attempting to match the typed entry to one of the DropDown list options even though only the first character matches.

In the Example workbook that I uploaded you will see that I clear any existing entry in the ComboBox field in the Enter Event so that there is no selection before starting to type.

If problem not resolved then please upload a zipped copy of your workbook as per my previous reply and I will attempt to assist further.

Private Sub cmbx_siteName_Enter()

    Me.cmbx_siteName.ListIndex = -1       'Clear value from the ComboBox and unselect any selection in the DropDown

    Me.cmbx_siteName.DropDown

End Sub

https://1drv.ms/u/s!AgN_6htswZGNuTypen4hH_lPuxNV?e=d7ZMjz

Sorry about that.

Unfortunately there appears to be something wrong with the workbook. It will initially open OK but if I make any changes and attempt to save it then it stalls and checking on Task Manager, it goes into 100% CPU usage and I can't do anything with it other than exit Excel via Task Manager.

The size of the file makes me suspect workbook corruption but I can't be certain of this. When you zipped the file after you initially uploaded the workbook without zipping, did you do so with a clean copy from your computer or did you zip the copy from OneDrive because if the latter then that could be the cause a problem.

You said "The file you sent is about what I am looking for". Therefore, if the workbook is working satisfactorily for you then I suggest that you comment out all of the code you have in the worksheet module and just copy the code I gave you and see if it does what you want. If it works then try adding the other functionally to the code I supplied. Don't forget you will need to add the defined name in Name Manager.

If you still can't get it working then if you did zip the file from OneDrive then please zip and upload a copy from your computer and upload again and I will have another look at it. Unfortunately because workbooks on OneDrive open automatically when accessed directly, some functionality is lost.