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