Actually, when using SetFocus on TextBox1, Textbox1 IS the control with
focus
but it seems like it just does not show it:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
userform1.TextBox1.SetFocus
Debug.Print UserForm1.ActiveControl.Name
end sub
Yes, I had already discovered that too; but what I don't get is why
re-showing the UserForm does not give focus to the active control on it. My
guess is that the UserForm gets focus (I'm pretty sure that is why my using
SendKeys to issue Tab key presses works), but my experience in the compiled
VB world tells me that when controls are situated on a form, the form cannot
retain focus (unless all controls have their TabStop set to False)... is
this not the case with UserForms?
Also, if you have other controls on the Userform, say Textbox2, you can
use
one these to SetFocus first, then to Textbox1
''' -------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
With UserForm1
.TextBox2.SetFocus
.TextBox1.SetFocus
End With
Application.ScreenUpdating = True
End Sub
Assuming there is at least one other control on your form capable of taking
focus, you don't have to "create" an extra control... you can use the
following more general routine which lets the code find some other control
to set focus first (without you having to specify it) before returning focus
to TextBox1...
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim Cntrl As Control
With UserForm1
.Show
For Each Cntrl In .Controls
If Not Cntrl Is .TextBox1 Then
Cntrl.SetFocus
Exit For
End If
Next
.TextBox1.SetFocus
End With
End Sub
Here I used a Workbook code event, but you can use the Sheet's events as
well.
Rick