Can I detect what Control the cursor is at in a form?

D

Dreiding

I dynamically build a form with textboxes.
Is there a way to detect which text box the cursor is at when a button is
clicked?

Tried and failed with the following code (not a surprise)
Function SelectedControl() as string
Dim ctl As Control
SelectedControl=""
For Each ctl In Me.Controls
If ctl.setfocus = true Then
SelectedControl=ctl.name
exit for
End If
Next ctl
End Sub

I suspect my problem is two-fold.
1. No way to detect current focus (can't read the SetFocus)
2. When the button is clicked, the focus moves..

Any help or suggestions appreciated.
tia, Pat
 
R

Rick Rothstein

You can do something like this... set up a form-wide global variable in the
form window's (General)(Declarations) section and Set this variable to the
control object in each control's Enter event. Maybe something like this, as
a starting framework, for example...

Dim LastControl As Control

Private Sub CommandButton1_Click()
Debug.Print LastControl.Name
End Sub

Private Sub TextBox1_Enter()
Set LastControl = TextBox1
End Sub

Private Sub TextBox2_Enter()
Set LastControl = TextBox2
End Sub

You would then be able to reference LastControl from your button (or from
anywhere else). Note, for this to work correctly, you would need to Set the
variable equal to Nothing at the end of all your other control's Enter
events...

Private Sub CommandButton1_Enter()
'
' <<<Your code>>>
'
Set LastControl = Nothing
End Sub

so that you can test LastControl where needed to make sure the last visited
control was a TextBox...

If Not LastControl Is Nothing Then
MsgBox "Last TextBox with focus was " & LastControl.Name
End If
 
J

Jacob Skaria

Option 1....

Dim ctlTemp As Control
Private Sub CommandButton1_Click()
MsgBox ctlTemp.Name
End Sub
Private Sub TextBox1_Enter()
Set ctlTemp = Me.ActiveControl
End Sub
Private Sub TextBox2_Enter()
Set ctlTemp = Me.ActiveControl
End Sub

Private Sub TextBox3_Enter()
Set ctlTemp = Me.ActiveControl
End Sub

Private Sub TextBox4_Enter()
Set ctlTemp = Me.ActiveControl
End Sub

Private Sub TextBox5_Enter()
Set ctlTemp = Me.ActiveControl
End Sub


Option 2 (will work only for mouse click)

Dim ctlTemp As Control
Private Sub CommandButton1_Click()
MsgBox ctlTemp.Name
End Sub
Private Sub CommandButton1_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Set ctlTemp = Me.ActiveControl
End Sub
 
D

Dave Peterson

Option Explicit
Private Sub CommandButton1_Click()
MsgBox Me.ActiveControl.Name & vbLf & Me.ActiveControl.Value
End Sub
Private Sub UserForm_Initialize()
With Me.CommandButton1
.TakeFocusOnClick = False 'never become the active control
.Caption = "Ok"
End With

With Me.CommandButton2
.TakeFocusOnClick = False
.Caption = "Cancel"
End With
End Sub
 
D

Dreiding

I really appreciate all the feedback and suggestions.
Unfortunately they will not do the job, but I understand the approaches.

The suggestions to use the "TextBox_Enter" wouldn't work because I can't
dynamically create the code. The TextBox names a driven from worksheet
tables.

The other suggestion to capture the previous control and/or not allow the
button to take focus didn't work because of the form structure.
I have my TextBoxes within a Frame which are all within a MultiPage. This
approaches always returns the MultiPage as the last control used.
Time to rethink.

-Thanks,
- Pat
 
D

Dave Peterson

It's usually a good idea to give those kind of details in the original post. It
makes it easier for the responders to come up with answers that apply.

If you only had frames or multipages (none included on the other), you could use
something like:

Option Explicit
Private Sub CommandButton1_Click()
Dim myCtrl As Control

Set myCtrl = Me.ActiveControl

If TypeOf myCtrl Is MSForms.MultiPage Then
With myCtrl
With .Pages(.Value).ActiveControl
MsgBox .Name & vbLf & .Value
End With
End With
ElseIf TypeOf myCtrl Is MSForms.Frame Then
With myCtrl.ActiveControl
MsgBox .Name & vbLf & .Value
End With
Else
With myCtrl
MsgBox .Name & vbLf & .Value
End With
End If

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.CommandButton1
.TakeFocusOnClick = False 'never become the active control
.Caption = "Ok"
End With

With Me.CommandButton2
.TakeFocusOnClick = False
.Caption = "Cancel"
End With
End Sub


But since you've got a textbox in a frame on a multipage, you could use
something like:


Option Explicit
Dim ActCtrl As Control
Private Sub CommandButton1_Click()
Dim myCtrl As Control

Set ActCtrl = Nothing
Call DrillDown(myCtrl:=Me.ActiveControl)

If ActCtrl Is Nothing Then
MsgBox "no active control"
Else
With ActCtrl
MsgBox .Name & vbLf & .Value
End With
End If
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.CommandButton1
.TakeFocusOnClick = False 'never become the active control
.Caption = "Ok"
End With

With Me.CommandButton2
.TakeFocusOnClick = False
.Caption = "Cancel"
End With
End Sub
Sub DrillDown(myCtrl As Control)

If TypeOf myCtrl Is MSForms.MultiPage Then
Call DrillDown(myCtrl:=myCtrl.Pages(myCtrl.Value).ActiveControl)
Else
If TypeOf myCtrl Is MSForms.Frame Then
Call DrillDown(myCtrl:=myCtrl.ActiveControl)
Else
Set ActCtrl = myCtrl
End If
End If
End Sub
 
D

Dreiding

Dave, Wow!

Thanks,
- Pat

Dave Peterson said:
It's usually a good idea to give those kind of details in the original post. It
makes it easier for the responders to come up with answers that apply.

If you only had frames or multipages (none included on the other), you could use
something like:

Option Explicit
Private Sub CommandButton1_Click()
Dim myCtrl As Control

Set myCtrl = Me.ActiveControl

If TypeOf myCtrl Is MSForms.MultiPage Then
With myCtrl
With .Pages(.Value).ActiveControl
MsgBox .Name & vbLf & .Value
End With
End With
ElseIf TypeOf myCtrl Is MSForms.Frame Then
With myCtrl.ActiveControl
MsgBox .Name & vbLf & .Value
End With
Else
With myCtrl
MsgBox .Name & vbLf & .Value
End With
End If

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.CommandButton1
.TakeFocusOnClick = False 'never become the active control
.Caption = "Ok"
End With

With Me.CommandButton2
.TakeFocusOnClick = False
.Caption = "Cancel"
End With
End Sub


But since you've got a textbox in a frame on a multipage, you could use
something like:


Option Explicit
Dim ActCtrl As Control
Private Sub CommandButton1_Click()
Dim myCtrl As Control

Set ActCtrl = Nothing
Call DrillDown(myCtrl:=Me.ActiveControl)

If ActCtrl Is Nothing Then
MsgBox "no active control"
Else
With ActCtrl
MsgBox .Name & vbLf & .Value
End With
End If
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.CommandButton1
.TakeFocusOnClick = False 'never become the active control
.Caption = "Ok"
End With

With Me.CommandButton2
.TakeFocusOnClick = False
.Caption = "Cancel"
End With
End Sub
Sub DrillDown(myCtrl As Control)

If TypeOf myCtrl Is MSForms.MultiPage Then
Call DrillDown(myCtrl:=myCtrl.Pages(myCtrl.Value).ActiveControl)
Else
If TypeOf myCtrl Is MSForms.Frame Then
Call DrillDown(myCtrl:=myCtrl.ActiveControl)
Else
Set ActCtrl = myCtrl
End If
End If
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I set focus (and select text) on a text box on a modeless userform? 0
HELP: Unable to clear values for control of type text in a userfor 4
Testing for required data entry in form fields. 6
Assign string to a Control 13
Taking control from Option Button into RefEdit in Excel 2010 behaving oddly 4
mimicking a control array 3
Keep form open until loop completes 2
Find textboxes on a form ? 4

Top