About Us

Dreiding

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).Activ eControl)
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
.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
2007 Form Control/ActiveX Control font difference	Nikko963	Excel Discussion (Misc queries)	0	April 15th 09 04:21 PM
cursor control	saneedshelp	Excel Programming	1	March 30th 07 06:52 PM
Control Cursor movement with Tab	Costi	Excel Discussion (Misc queries)	7	February 21st 07 06:47 AM
Tool Tip Text for Form control/ Active-X control	Freddie[_2_]	Excel Programming	0	October 19th 04 04:14 AM
How to get Control M to move cursor down	Dag Adamson	Excel Programming	2	December 20th 03 03:33 PM

Menu

About Us