SetFocus on ComboBox

dragonfyre77

New Member
Joined
Apr 6, 2011
Messages
9
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.



Code:
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

dragonfyre77

New Member
Joined
Apr 6, 2011
Messages
9
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

 

Watch MrExcel Video

Forum statistics

Threads
1,123,141
Messages
5,599,969
Members
414,353
Latest member
ljhan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top