SetFocus on ComboBox

[dragonfyre77]

I have setup a file that I am using to manage my fantasy football league's auction draft. To ensure all information is being recorded in the appropriate pages I am using a userform to input all the necessary information (some which are manually entered or selected from a combobox and other that are autopopulated based on other input fields). Once a player is drafted to a team his information his recorded on Master draft sheet, the drafting teams sheet and position sheet is updated with the drafting teams name which acts as an indicator that this player is no longer available to be drafted.

However, because the night is long and many players are being called out there are occasions where a player is called twice. With the code I have inserted below upon exiting the rank text box a check is performed to identify is the player has been drafted previously and if they have a message box appears asking for a new player to be selected. The userform is then cleared and is supposed to reset to the PositionSheet ComboBox (tabindex = 0). This is where the error occurs.

The messagebox appears like it should stating that the player has been drafted already and a new player needs to selected. However upon clicking the OK button the userform is cleared (like it should be) but another message box appears this time with no player name being called. Once the second OK button is clicked again the cursor appears at the Salary textbox.

I'm thinking its the PositionSheet.Set Focus is what is causing the issue because when I remove that piece of code it works almost like I want with the exception of the cursor appearing at the TeamName ComboBox. This would be fine however I am trying to make this userform as efficient as possible to help the person in charge of running our draft.

I've scoured the forums and tried various different things but to no avail. Wondering what I can do to get this to work and have the cursor appear at PositionSheet without triggering the message box twice.

Private Sub Rank_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Player As String
Dim Status As String
Dim DupFind As Range

On Error GoTo ErrHandler

'Returns the Player Name from the corresponding position sheet

        Player = Application.WorksheetFunction.VLookup(CInt(Rank), Worksheets(PositionSheet.Value).Range("a2:b500"), 2, False)
        PlayerName = Player
        
'Determines whether the player being selected has been drafted earlier.  If the player has been drafted already a message is prompted
'stating a new player must be selected and resets the userform.
        
With Sheets(PositionSheet.Value).Columns(2)
    Set DupFind = .Find(What:=Me.PlayerName.Value, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
End With


If Not IsEmpty(DupFind.Offset(, 5)) Then
MsgBox PlayerName.Value & " has already been drafted. Please select again."
Clear_Form
PositionSheet.SetFocus

Else

End If

ErrHandler:
        Resume Next

End Sub

Sub Clear_Form()

For Each Ctrl In Me.Controls
Select Case TypeName(Ctrl)
    Case "TextBox"
        Ctrl.Text = ""
        
    Case "ComboBox"
        Ctrl.ListIndex = -1
        
End Select

Next
End Sub

 

[dragonfyre77]

I have attached a link to download the file to assist in sussing out a solution.

The test I have been running is to select QB in the first combobox and type 1 and tab to TeamName combobox which prompts the message boxes.

Auction Draft Machine