Originally Posted by
kevvukeka
...I have created a userform. My team would copy data from other applications and paste it in different text boxes on userform.
Whenever they use ALt+Tab to toggle between different other excel sheets or application and come back to userform the focus is not staying in userform...
Greetings,
I am not exactly sure if this does what you want; I hope so. I noted that you mention switching to other applications, and I started thinking that checking what window has the focus may be a way to go.
Anyways, the basic bits are...
In the Userform's Module:
Option Explicit
'===API CONSTANTS
'None
'===API
Private Declare Function GetForegroundWindow Lib "user32" () As Long
Private Declare Function GetActiveWindow Lib "user32" () As Long
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function SetTimer Lib "user32" (ByVal hWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32" (ByVal hWnd As Long, _
ByVal nIDEvent As Long) As Long
' Previous variables
Public wb1 As Workbook, iwb2 As Workbook
Public i As Long
'===Added variables
'===Properties
Private hWndForm As Long
Private lTimerRet As Long
Private bLostFocus As Boolean
Private sActiveCtrlName As String
'Read-Only
Public Property Get FormHandle() As Long
FormHandle = hWndForm
End Property
Public Property Get Control01Name() As String
Control01Name = "TextBox1"
End Property
Public Property Get Control02Name() As String
Control02Name = "TextBox2"
End Property
'Read-Write
Public Property Let FocusLost(fl As Boolean)
bLostFocus = fl
End Property
Public Property Get FocusLost() As Boolean
FocusLost = bLostFocus
End Property
Public Property Let ActiveControlName(acn As String)
sActiveCtrlName = acn
End Property
Public Property Get ActiveControlName() As String
ActiveControlName = sActiveCtrlName
End Property
Public Function ForeWndHwnd() As Long
ForeWndHwnd = GetForegroundWindow()
End Function
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim lKillTimerRet As Long
If CloseMode = 0 Then
Cancel = True
MsgBox "Please use the Exit button on the Userform"
Else
'// If we are unloading the form, kill the timer. //
lKillTimerRet = KillTimer(hWndForm, lTimerRet)
End If
'Debug.Print "Timer killed in QueryClose = " & (lKillTimerRet <> 0) & "; Cancel = " & CBool(Cancel)
End Sub
Private Sub UserForm_Initialize()
With Me
'// Change caption to something unlikely to exist in another titlebar, get the handle //
'// to this form's window, change the caption back. //
.Caption = "123123123123"
hWndForm = FindWindow("ThunderDFrame", .Caption)
.Caption = "TimeSheet - " & Application.UserName
'// Cursory check //
If TypeName(.ActiveControl) = "ListBox" Or TypeName(.ActiveControl) = "TextBox" Then
sActiveCtrlName = .ActiveControl.Name
End If
End With
'// Set a timer, passing a pointer to Me. //
lTimerRet = SetTimer(hWndForm, ObjPtr(Me), 50&, AddressOf TimerProc)
Set wb1 = ThisWorkbook
'code to get username and date on initializing the userform
TextBox1.Value = Application.UserName
TextBox2.Value = Format(CDate(Date), "DD-MMM-YYYY")
'code to add values to the listbox (Task List)
With ListBox1
For i = 2 To wb1.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
.AddItem wb1.Sheets(1).Range("A" & i).Value
Next i
End With
End Sub
And the callback in a Standard Module:
Sub TimerProc(ByVal hWnd As Long, ByVal uMsg As Long, ByVal oForm As TSUF, ByVal dwTime As Long)
Dim lRet As Long
Dim lPrevHwnd As Long
Dim sJumpFromCtrl As String
'// It would probably be easier just to stick the API functions in this Standard Module.//
'// I had just started writing it this way. //
lRet = oForm.ForeWndHwnd
'// Test to see if the fore window is the form, or any other window. //
If Not lRet = oForm.FormHandle Then
oForm.FocusLost = True
Else
'// If the fore window IS the form, then see if it just received focus again, and if //
'// so... //
If oForm.FocusLost Then
oForm.FocusLost = False
'// ...try and set the focus to the last active control. //
On Error Resume Next
If Len(oForm.ActiveControlName) > 0 Then
If oForm.ActiveControlName = oForm.Control01Name Then
sJumpFromCtrl = oForm.Control02Name
Else
sJumpFromCtrl = oForm.Control01Name
End If
oForm.Controls(sJumpFromCtrl).SetFocus
DoEvents
'lPrevHwnd = SetFocusAPI(oForm.Frame1.[_GethWnd])
'Debug.Print lPrevHwnd
oForm.Controls(oForm.ActiveControlName).SetFocus
End If
On Error GoTo 0
'// Else, if the form does have focus and did not just receive it, track what control //
'// is active. //
Else
If TypeName(oForm.ActiveControl) = "ListBox" Or TypeName(oForm.ActiveControl) = "TextBox" Then
oForm.ActiveControlName = oForm.ActiveControl.Name
ElseIf TypeName(oForm.ActiveControl) = "Frame" Then
oForm.ActiveControlName = oForm.Frame1.ActiveControl.Name
End If
End If
End If
End Sub
I attached your workbook with these mods. Does that help?
Mark
Reply With Quote
