Use of Userform vbModeless

[mchac]

I have ActiveX textboxes on a sheet where a user keys values. I'd like the user to be able to continue to key in values without interruption but have a userform appear that allows the user to click on a button to submit the values they've just input in the textboxes or exit without submitting.

Currently when I test my code and type into a textbox the userform appears and the focus is changed to the userform. I can click on the textbox again to reset the focus but I'd prefer the userform to appear without changing focus.

This is the code i'm using is from here:
SetFocus For VBA UserForms
but i'm doing something wrong and cant understand what it is.

Any help is appreciated.

Userform code sheet:

Option Explicit
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" ( _
    ByVal hWnd1 As Long, _
    ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" ( _
    ByVal HWnd As Long, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    lParam As Any) As Long
Private Const WM_SETFOCUS = &H7


Private Sub SetSheetFocus()
    Dim HWND_XLDesk As Long
    Dim HWND_XLApp As Long
    Dim HWND_XLSheet As Long
    HWND_XLApp = Application.HWnd
    HWND_XLDesk = FindWindowEx(HWND_XLApp, 0&, "XLDESK", vbNullString)
    HWND_XLSheet = FindWindowEx(HWND_XLDesk, 0&, "EXCEL7", ActiveWindow.Caption)
    SendMessage HWND_XLSheet, WM_SETFOCUS, 0&, 0&
End Sub


Private Sub UserForm_Activate()
    
    SetSheetFocus


    Me.StartUpPosition = 0
    Me.Top = Application.Top + 75
    Me.Left = Application.Left + Application.Width - Me.Width - 25


End Sub

Worksheet codesheet

Private Sub textbox200_change()


    Dim ROW_NUM As Long
    Dim DUE_DATE As Date
    Dim AMOUNT As Double
    
    Load UserForm2
    UserForm2.Show vbModeless
    
    ROW_NUM = ActiveSheet.Shapes("TextBox200").TopLeftCell.Row
        
    DUE_DATE = ActiveSheet.Range("E" & ROW_NUM).Value
    AMOUNT = ActiveSheet.Range("F" & ROW_NUM).Value
    
    TextBox201 = Format(CDbl(TextBox200.Value) / 100 / (DUE_DATE - Date) * 366 * 100, "#.###")
    TextBox202 = Format(AMOUNT * CDbl(TextBox200.Value) / 100, "$#,###.##")
    TextBox203 = Format(AMOUNT - AMOUNT * CDbl(TextBox200.Value) / 100, "$#,###.##")
    TextBox204 = Format(100, "###.00")
    
End Sub

 

[AlphaFrog]

I'm not sure why the SetSheetFocus function doesn't work. A simple alternative is to put AppActivate ThisWorkbook.Application in the textbox200_change procedure.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] textbox200_change()
    
    [COLOR=darkblue]Dim[/COLOR] ROW_NUM [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] DUE_DATE [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Date[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] AMOUNT [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
    
    [COLOR=green]'Load UserForm1[/COLOR]
    UserForm1.Show vbModeless
    [B]AppActivate ThisWorkbook.Application[/B]
        
    ROW_NUM = ActiveSheet.Shapes("TextBox200").TopLeftCell.Row
    
    DUE_DATE = ActiveSheet.Range("E" & ROW_NUM).Value
    AMOUNT = ActiveSheet.Range("F" & ROW_NUM).Value
    
    TextBox201 = Format(CDbl(textbox200.Value) / 100 / (DUE_DATE - [COLOR=darkblue]Date[/COLOR]) * 366 * 100, "#.###")
    TextBox202 = Format(AMOUNT * [COLOR=darkblue]CDbl[/COLOR](textbox200.Value) / 100, "$#,###.##")
    TextBox203 = Format(AMOUNT - AMOUNT * [COLOR=darkblue]CDbl[/COLOR](textbox200.Value) / 100, "$#,###.##")
    TextBox204 = Format(100, "###.00")
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

And remove the SetSheetFocus from the UserForm_Activate procedure.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Activate()
    
    [B][COLOR=green]'SetSheetFocus[/COLOR][/B]
        
    Me.StartUpPosition = 0
    Me.Top = Application.Top + 75
    Me.Left = Application.Left + Application.Width - Me.Width - 25
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[mchac]

genius.
that worked. thank you.

 

[AlphaFrog]

You're welcome. Thanks for the feedback.