set focus to form

[Gus Chuch]

How would you set focus back to a form once a new cell has been clicked ?
I’v tried the following but no luck
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UserForm1.TextBox1.SetFocus
End Sub
Any ideas?

 

[sebastienm]

Hi ,
Do you mean you want the form to show?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UserForm1.Show
End Sub

 

[Gus Chuch]

No the form is already shown. I show the form in the workbook open sub. i
just would like to have the textbox get focus again when I click on a new
cell.

 

[Rick Rothstein \(MVP - VB\)]

Okay, this is a kludge, I mean a **real** kludge, but it does seem to work.
Copy/paste this event code into the code window for the WorkBook....

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
UserForm1.Show
If UserForm1.ActiveControl.TabIndex <> UserForm1.TextBox1.TabIndex Then
SendKeys "+{TAB " & UserForm1.ActiveControl.TabIndex & "}", True
Else
SendKeys "{TAB}", True
SendKeys "+{TAB}", True
End If
End Sub

I'm sure there must be a "cleaner" way to do this, and hopefully someone
will come along with that solution; but, until then, the above should at
least let you continue working.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Not that it is all that critical, but we can condense the two statements in
the Else block down to a single statement...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
UserForm1.Show
If UserForm1.ActiveControl.TabIndex <> UserForm1.TextBox1.TabIndex Then
SendKeys "+{TAB " & UserForm1.ActiveControl.TabIndex & "}", True
Else
SendKeys "{TAB}+{TAB}", True
End If
End Sub

Rick

 

[Rick Rothstein \(MVP - VB\)]

And then, using the same idea from my last post, we can eliminate the entire
If/Else/Then block altogether...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
UserForm1.Show
SendKeys "{TAB}+{TAB " & 1 + UserForm1.ActiveControl.TabIndex & "}", True
End Sub

Rick

Not that it is all that critical, but we can condense the two statements
in the Else block down to a single statement...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
UserForm1.Show
If UserForm1.ActiveControl.TabIndex <> UserForm1.TextBox1.TabIndex Then
SendKeys "+{TAB " & UserForm1.ActiveControl.TabIndex & "}", True
Else
SendKeys "{TAB}+{TAB}", True
End If
End Sub

Rick

 

[sebastienm]

ok I see.
Seems like the behavior (or lack of) only happens when the control is the
first one on the userform. That is, I added a non-visible TextBoxDummy, and
in the userform _Activate event, i do:

Private Sub UserForm_Activate()
TextBoxDummy.Visible = True
TextBoxDummy.SetFocus
TextBox1.SetFocus
TextBoxDummy.Visible = False
End Sub

Seems to be working.

 

[Rick Rothstein \(MVP - VB\)]

You are getting the UserForm Activate to fire when a **new** cell is
selected???

When I try it, the Activate event for the UserForm does not fire.

Rick

 

[sebastienm]

oops my bad, i thought I had already posted the following code in my 1st post:
Since Gus said the form is already shown when the user click a cell, i
assumed it is shown Modeless, am I correct?
On _Selection I hide then show the form to generate its _Activate event.
''' -----------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
With UserForm1
.Hide
.Show vbModeless
End With
Application.ScreenUpdating = True
End Sub
''' ----------------------------------------------------------------------

 

[Rick Rothstein \(MVP - VB\)]

Hide, then show the screen immediately afterwards and use a false control
with TabIndex 0... okay, I see what you did... you used a different kludge
than I did.<g> Yes, by the way, I assumed the UserForm was Modeless; but you
don't have to specify that in the Show statement (the way you are doing)....
it seems the fact that the ShowModal property was set to False at design
time (that pretty much has to be the case I would think) means you don't
have to re-specify that setting every time you re-show the hidden UserForm.

What seems strange to me is that there isn't a kludge-less solution to this
question. It seems that executing the Show method sends the focus somewhere,
but I am not sure where... pressing various keys has no effect until you
press the Tab key, which then locates the control whose TabIndex is next
after the previous ActiveControl on the UserForm (hence my SendKeys kludge).
Before using the Tab key, the focus seems to be in limbo.

Rick

oops my bad, i thought I had already posted the following code in my 1st
post:
Since Gus said the form is already shown when the user click a cell, i
assumed it is shown Modeless, am I correct?
On _Selection I hide then show the form to generate its _Activate event.
''' -----------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
With UserForm1
.Hide
.Show vbModeless
End With
Application.ScreenUpdating = True
End Sub
''' ----------------------------------------------------------------------

 

[sebastienm]

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

I now tried a frew more things to group the workaround code together and
trim it.

By moving the code from _Activate to _SelectionChange,it works too. It makes
the code for workaround a little bit less spread.
''' -------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
With UserForm1
.TextBoxDummy.Visible = True
.TextBoxDummy.SetFocus
.TextBox1.SetFocus
.TextBoxDummy.Visible = False
End With
Application.ScreenUpdating = True
End Sub
''' ----------------------------------------------

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
''' ----------------------------------------------

Finally, for second control, you can choose a Frame with SpecialEffect set
to Flat and no caption; that is, no need of hidding it and no need of
changing the ScreenUpdating
''' -------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UserForm1.Frame1.SetFocus
UserForm1.TextBox1.SetFocus
End Sub
''' ----------------------------------------------

 

[Rick Rothstein \(MVP - VB\)]

Last revision, I promise.<g>

It seems that my previous code required the TextBox to be first in the Tab
order (that is, it had to have its TabIndex be zero); the code below removes
that restriction.... the control you want to get focus (assumed to be
TextBox1 for this example) when a new cell is clicked can be anywhere in the
Tab order.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
UserForm1.Show
SendKeys "{TAB " & UserForm1.TextBox1.TabIndex & "}+{TAB " & _
UserForm1.ActiveControl.TabIndex & "}", True
End Sub

Rick

And then, using the same idea from my last post, we can eliminate the
entire If/Else/Then block altogether...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
UserForm1.Show
SendKeys "{TAB}+{TAB " & 1 + UserForm1.ActiveControl.TabIndex & "}", True
End Sub

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

Sure, I said "assuming there is at least one other control on your form
capable of taking focus" and then promptly forgot about it. Here is modified
code to account for controls that can't take focus (like a Label)...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim Cntrl As Control
With UserForm1
.Show
On Error Resume Next
For Each Cntrl In .Controls
If Not Cntrl Is .TextBox1 Then
Cntrl.SetFocus
If Err.Number = 0 Then Exit For
End If
Next
.TextBox1.SetFocus
End With
End Sub

Rick

 

[Gus Chuch]

no that did'nt work. But I did figure out that if I do a SendKeys ("%{F6}") I
get the UserForm to be activate again. But I still can’t bring focus back to
my textbox1 without clicking on it.

 

[Rick Rothstein \(MVP - VB\)]

Nope, not the last revision... I left out the forced movement required if
TextBox1 is the first control in the Tab order. This should be the final
revision...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
UserForm1.Show
SendKeys "{TAB " & 1 + UserForm1.TextBox1.TabIndex & "}+{TAB " & _
1 + UserForm1.ActiveControl.TabIndex & "}", True
End Sub

Rick

 

[Rick Rothstein \(MVP - VB\)]

Did you put the code I posted in the Workbook (not the Worksheet) code
window? The Title for the window will say...

Book1 - ThisWorkbook (Code)

although the number attached to the word Book could be a different number.
To get to this window, double click the ThisWorkbook entry in the Project
Explorer listing on the left. By the way, I used the Workbook module because
that way the functionality would be available from any worksheet in the
workbook. Oh, and make sure you use the much shorter last code that I
posted. Well, I just posted it (there was a correction), and it is short
enough, so I'll save you the trouble of locating it; here it is...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
UserForm1.Show
SendKeys "{TAB " & 1 + UserForm1.TextBox1.TabIndex & "}+{TAB " & _
1 + UserForm1.ActiveControl.TabIndex & "}", True
End Sub

Rick

no that did'nt work. But I did figure out that if I do a SendKeys
("%{F6}") I
get the UserForm to be activate again. But I still can’t bring focus back
to
my textbox1 without clicking on it.

 

[Gus Chuch]

It works but why do we have to use that second textbox? I guess I don’t
really understand what’s going on.

 

[Rick Rothstein \(MVP - VB\)]

It doesn't have to be a TextBox per se, all it needs to be is another
control capable of taking focus (both Sébastien's and my solutions depend on
that). The problem appears to be a bug which prevents the control whose
TabIndex is 0 from receiving focus using the UserForm1.Show command; so both
our solutions use a sort of kludge to get around the problem. The odds are
your UserForm will have more than one control on it, so you can use any one
of them for Sébastien's solution without needing to hide anything (Sébastien
covers this in one of his postings) or it will automatically be used by the
solution I posted (and which hopefully you now have working).

Rick