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

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

--
Rick (MVP - Excel)


"Dreiding" <(E-Mail Removed)> wrote in message
news:CE4B2938-31A7-4799-AC65-(E-Mail Removed)...
>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

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

--
Jacob


"Dreiding" wrote:

> 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

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

Dreiding wrote:
>
> 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

--

Dave Peterson

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

"Dreiding" wrote:

> 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

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

Dreiding wrote:
>
> 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
>
> "Dreiding" wrote:
>
> > 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

--

Dave Peterson

Dave, Wow!

Thanks,
- Pat

"Dave Peterson" wrote:

> 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
>
> Dreiding wrote:
> >
> > 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
> >
> > "Dreiding" wrote:
> >
> > > 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
>
> --
>
> Dave Peterson
> .
>