Set focus to Userform

[GettingThere]

Hi guys - Seem to have a silly little barrier:

Private Sub Workbook_Open()
If Application.Visible = True Then Application.Visible = False
Dim ssEIN As String
    ssEIN = InputBox("Please enter your Employee ID number", _
        "Welcome to the Admin Assisstant", "Please type your m or cx number")
Load frmLnD
GetUserLogin (ssEIN)
frmLnD.Show
'How do you ensure the form is activated?!?!
End Sub

As Excel is hidden and the userform cannot be shown until the GetUserLogin Function is run the userform is being displayed without focus - how do I set focus to a userform in this way?!?!

Thanks for any feedback.


Cheers,

A.

 

[Andrew Poulsom]

What is GetUserLogin?

 

[GettingThere]

It's just calling another sub in another module to idetify the staff member using it (used later for access to different subs/mdb tables):

Sub GetUserLogin(ByRef sEIN As String)
If sEIN = "" Then
    If Application.Visible = False Then Application.Visible = True
    ActiveWorkbook.Close (False)
End If
'''''Declarations
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL1 As String
Dim sSQL2 As String
Dim sUser As String
'''''Database Connection
myConn = Sheets("Impact").Range("a1").Value
'''''Set up Conn
Set cnn = New ADODB.Connection
With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0; "
    .Open myConn
End With
'''''Set up rs
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
 
reTry:
With frmLnD
sSQL1 = "SELECT * FROM [staff]" & _
        " WHERE EIN = '" & sEIN & "'"
rs.Open (sSQL1), cnn, adOpenDynamic, adLockOptimistic, adCmdText
End With
If rs.EOF = True Then
    sUser = InputBox("Your log in has not been recognised." & vbCrLf & vbCrLf & _
           "Please check with Admin to ensure your user has been set up/still exists and try again." & vbCrLf & vbCrLf & _
           "Alternatively, click cancel to close", "Log in not recognised", "Please type your m or cx number")
    Select Case sUser
        Case ""
            ActiveWorkbook.Close (False)
        Case Else
            sEIN = sUser
            rs.Close
            GoTo reTry
    End Select
Else
    With frmLnD
        ''Amend existing record
        .lblEIN = rs("ein")
        .lblUser = rs("FirstName") & " " & rs("Surname")
    End With
End If
'''''Close Connections
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

My only problem is that after it runs and I show the form frmLnD it doesn't have focus... It's not a big issue - obviously the user could just click on it but it does look very unprofessional and there wil be c. 500 people using this... lmao =D

Cheers

 

[Andrew Poulsom]

I can't reproduce your problem in Excel 2000. I added a UserForm with a TextBox and a CommandButton. With this code:

'ThisWorkbook module
 
Private Sub Workbook_Open()
    If Application.Visible = True Then Application.Visible = False
    frmLnD.Show
End Sub
 
'UserForm module
 
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Application.Visible = True
End Sub

opening the workbook caused the UserForm to be shown with the focus on the TextBox.

 

[GettingThere]

this should replicate it my problem (I'm currently on Excel 2002 btw)....

Private Sub Workbook_Open()
    If Application.Visible = True Then Application.Visible = False
    sInput = InputBox("Input something", , "Type something here")
    UserForm1.Show
End Sub

It's losing focus beause I've got an input box before showing the userform...

thanks for your time on this.


Cheers,

A.

 

[Andrew Poulsom]

Nope, the focus is still on the TextBox.

 

[GettingThere]

??

Not sure what's going on then - I created a new workbook and did just as you did - one textbox, one cmd button and just those two bits of code and everytime it loses focus...

So - there's no way you can think of which would be the equivalent of: Userform1.SetFocus...

Oh well... lol - I'll see what I can do to re-arrange the order of things on initialisation....


Cheers,

A.