ActiveControl problem

[DAACKM]

I am attempting to service several Excel controls from the same macro code as
shown below:

Sub WHOAMI()
Dim ctrl As Control
Dim getactivectlname, mytext
ctrl = screen.ActiveControl
mytext = ctrl.Name
MsgBox ("The Value Is " & ctrl & " text= " & mytext & " thats all")
End Sub

WHOAMI launches, but on the "CTRL = SCREEN.ACTIVECONTROL" statement I get a
"Runtime Error 424 -- Object Required" error.

This is on Excel XP (2002). Any Ideas? Currently I have no DECLARATIONS.

Thanks for the help. Dave

 

[Harald Staff]

Hi Dave

See if this is of use:

Sub WHOAMI()
MsgBox Application.Caller
End Sub

HTH. Best wishes Harald

 

[Dave Peterson]

Can you explain where the controls are located (on a worksheet or on a
userform)?

If they are on a worksheet, did you get them from the Forms toolbar or from the
Control toolbox toolbar or even from the drawing toolbar?

If they are on a worksheet and you got them from the Forms toolbar (or the
drawing toolbar), you may want to look at application.caller.

If they are on a worksheet and you got them from the Control toolbox toolbar,
you may want to look at John Walkenbach's site:

http://spreadsheetpage.com/index.php/tip/handle_multiple_userform_buttons_with_one_subroutine/

If the controls are on a userform, then I'm not sure how you run WhoAmi, but
this may give you an idea.

Option Explicit
Private Sub CommandButton1_Click()

Dim ctrl As Control
Dim myText As String

Set ctrl = Me.ActiveControl

myText = ctrl.Name
MsgBox "The Value Is " & ctrl.Value & vbLf _
& "text= " & myText & vbLf _
& " thats all"

End Sub
Private Sub UserForm_Initialize()
'so it doesn't become the active control
Me.CommandButton1.TakeFocusOnClick = False
End Sub

 

[DAACKM]

Thanks, Harald. This was just what I needed.