If TargetAddress vs If TextBoxes(userform controls)

[Flexcel22]

Hi Everyone,

I remembered working with If TargetAddress which works with pointers. It really amazing using it to do manipulating since all you do is just use a tab key or arrows.

I wondered if this can be applied in a userform.

My case:

I had a userform with many textboxes of around 60. All with the same size and font. There's one textbox which is 6 times bigger in size and font than the remaning textboxes.
I wanted to use the bigger textbox to keep displaying an active smaller textbox content. In this way when I press tab key on my keyboard to jump from textboxt to textbox, the bigger one will automatically display the content of whichever is selected or activated.

Any idea?

 

[Jaafar Tribak]

You could use a generic class module to hook all textboxes or you could use the following workaround :

Code in the UserForm Module:

Option Explicit

Private bClosing As Boolean

Private Sub UserForm_Activate()
    Do
        If TypeName(ActiveControl) = "TextBox" Then
            [COLOR=#ff0000][B]BigTextBoxNameHere [/B][/COLOR]= ActiveControl.Value
        End If
        DoEvents
    Loop Until bClosing
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    bClosing = True
End Sub

Replace the text in red in the above code with the actual name of the specific Big TextBox

 

[Flexcel22]

Hi Jafaar,

I have tried your code and also replaced the redtext to my big textbox name and unfortunately, nothing happen to the big textbox. The big textbox never changed value at all and it always stays blank.

I had no idea why it happened but I hope you can demonstrate more.

Thanks for your time and hope to hear more from you.

 

[My Aswer Is This]

Try this:

Put this script in your UserForm:

Sub Loop_Me()
For Each Control In Me.Controls
If TypeOf Control Is MSForms.TextBox Then TextBox1.Value = ActiveControl.Value
Next
End Sub

Now put this script short bit of code in every TextBox except for "TextBox1"

Call Loop_Me

It's best to always use Exit script on Textboxes Like this:

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Loop_Me

'And then do other stuff
End Sub

Then when you exit any Textbox on your UserForm the value entered in that textbox will also be loaded into Textbox1. Which you can set the font size to a larger size.

 

[Flexcel22]

Try this:

Put this script in your UserForm:

Sub Loop_Me()
For Each Control In Me.Controls
If TypeOf Control Is MSForms.TextBox Then TextBox1.Value = ActiveControl.Value
Next
End Sub

Now put this script short bit of code in every TextBox except for "TextBox1"

Call Loop_Me

It's best to always use Exit script on Textboxes Like this:

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call Loop_Me

'And then do other stuff
End Sub

Then when you exit any Textbox on your UserForm the value entered in that textbox will also be loaded into Textbox1. Which you can set the font size to a larger size.

Hi There,

What if my controls are in Multipage1?

I get the error saying invalid use of Me.Controls. Should I include MultiPage1 too?How?

 

[My Aswer Is This]

I really do not know. You never mentioned in your original post about having your Textboxes in Multipages. Maybe someone else here at Mr. Excel will be able to help you.

Hi There,

What if my controls are in Multipage1?

I get the error saying invalid use of Me.Controls. Should I include MultiPage1 too?How?

 

[Jaafar Tribak]

See if this works for you even if you have textboxes inside MultiPages :

Code in tthe UserForm Module:

Option Explicit

Private bClosing As Boolean


Private Sub UserForm_Activate()
    Dim ctl As Control
    Dim lCurX As Long
    On Error Resume Next

    [COLOR=#008000]'If you have other code run it here before the loop..[/COLOR]

    Do
        For Each ctl In Me.Controls
            If TypeName(ctl) = "TextBox" Then
                lCurX = ctl.CurX
                If Err.Number = 0 Then
                   [COLOR=#ff0000] [B]BigTextBoxNameHere [/B][/COLOR] = ctl.Value: Exit For
                End If
            End If
            Err.Clear
        Next
        DoEvents
   Loop Until bClosing
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    bClosing = True
End Sub

Note that this code runs a continuous loop upon showing the userform which depending on other code that you may have , it can cause some issues.

The propper way to go is, I think, using a generic Class Module.

 

[Flexcel22]

See if this works for you even if you have textboxes inside MultiPages :

Code in tthe UserForm Module:

Option Explicit

Private bClosing As Boolean


Private Sub UserForm_Activate()
    Dim ctl As Control
    Dim lCurX As Long
    On Error Resume Next

    [COLOR=#008000]'If you have other code run it here before the loop..[/COLOR]

    Do
        For Each ctl In Me.Controls
            If TypeName(ctl) = "TextBox" Then
                lCurX = ctl.CurX
                If Err.Number = 0 Then
                   [COLOR=#ff0000] [B]BigTextBoxNameHere [/B][/COLOR] = ctl.Value: Exit For
                End If
            End If
            Err.Clear
        Next
        DoEvents
   Loop Until bClosing
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    bClosing = True
End Sub

Note that this code runs a continuous loop upon showing the userform which depending on other code that you may have , it can cause some issues.

The propper way to go is, I think, using a generic Class Module.

Hello Jaafar,

Your final code worked perfectly the way I wanted. I didnt change anything except the redtext to my textbox name and when I executed the code, BOOM!!! Everything runs smoothly as desired.

Thanks a million Jaafar. You have saved my day.

Thank you too "My Aswer is this" for contributing in this thread. It might not worked for me but it might be of great importance to whoever is searching for such a solution.

All the best again to you Jaafar for your wonderful solution.

 

[My Aswer Is This]

Well that does work. Glad you received a good answer. Not sure why mine would not work and you were not required to put script in each textbox which was great. I do not understand the script. But glad it worked for you.

 

[Jaafar Tribak]

@Flexcel22
Thanks for the feedback and glad I could help.

@My Answer Is This

I do not understand the script. But glad it worked for you.

The script iterates all the textboxes in the userform and retrieves the CurX Property of each TextBox. If the TextBox doesn't currently have the focus, it generates an error. If the textbox has the focus no error is generated... I take advantage of this to know which textbox is currently active.. Once I find the active textbox that has the focus, I just pass its value to the main textbox and exit the iteration.