How to pass an argument to the active control (vba)

[kelly mort]

I want a way that I could pass an argument to the active or current control that I have selected.

For example, when I double click on a textbook, there is this code that I would run.

Instead of placing the code under each textbox, I want a way where I would create a separate Sub for the code then call it each time I double click a textbox.

The issue now is how to be able to know which textbox to point to from the sub.

All the codes that will run before pointing to the textbox does not depend on the various textboxes.

But they are a bit long - the reason why I want to avoid writing them one by one.

So say I double click textbox1 then after running all my verification code, what I do is unlock the textbox1 because it has been locked before I do the double click.

Is there a way around it?

 

[RoryA]

If you assign a macro to a shape, you can get a reference to the shape using Activesheet.Shapes(application.caller).

 

[kelly mort]

If you assign a macro to a shape, you can get a reference to the shape using Activesheet.Shapes(application.caller).

Hello,

I don't really understand your syntax properly so I came across this syntax somewhere :

Me.ActiveControl

But when I tried it gave me the name of the multi page form I used instead of the textbox I double click

 

[RoryA]

You didn’t specify where the text box was so I guessed a worksheet. Is your text box locked or disabled?

 

[kelly mort]

You didn’t specify where the text box was so I guessed a worksheet. Is your text box locked or disabled?

The textbox is on a userform.

It could be it locked or unlocked.
But would always be enabled.

From the userform, I have a multi page form then I also have a frame on the page before the textbox

Me.ActiveControl.SelectedItem.ActiveControl

Got me to the frame

Then all my tricks disappeared

 

[kelly mort]

Bump

Still need help with this

 

[mikerickson]

You might use this UDF which drills down through Frames and MultiPages and returns the control that is active.

' in userform's Code Module

Public Function ReallyActiveControl(Optional Container As Object) As MSForms.Control
    If Container Is Nothing Then
        Set Container = Me
    End If
   
    On Error Resume Next
    Set ReallyActiveControl = Container.ActiveControl
    Set ReallyActiveControl = Container
    On Error GoTo 0
   
    On Error Resume Next
    Do
        If TypeName(ReallyActiveControl) = "MultiPage" Then
            With ReallyActiveControl
                Set ReallyActiveControl = .Pages(.Value)
            End With
        End If
        Set ReallyActiveControl = ReallyActiveControl.ActiveControl
    Loop Until Err
    On Error Goto 0
End Function

 

[kelly mort]

The udf is giving me the multi page form instead of the textbox

Check how I tested it

 

[mikerickson]

I see one typo, the Pages property of a multipage is plural.

If TypeName(ReallyActiveControl) = "MultiPage" Then
    With ReallyActiveControl
        Set ReallyActiveControl = .Pages(.Value)
    End With
End If

 

[kelly mort]

I see one typo, the Pages property of a multipage is plural.

If TypeName(ReallyActiveControl) = "MultiPage" Then
    With ReallyActiveControl
        Set ReallyActiveControl = .Pages(.Value)
    End With
End If

Sure, I fixed it later but still didn't get the right textbox I interacted with