Getting the Active Object "Name" or "Value" in VBA

[Rich Wills via AccessMonster.com]

It seem simple but can't work out how to refer to the "Current" or "Active"
field/control etc within VBA. Is there a way of doing this?

For example in Excel Macro's you could type...

activecell.value
activecell.row
activeobject.name

etc...

....but I can't seem to do this in VBA can anyone help?

Thanks

Rich

 

[Dirk Goldgar]

It seem simple but can't work out how to refer to the "Current" or
"Active" field/control etc within VBA. Is there a way of doing this?

For example in Excel Macro's you could type...

activecell.value
activecell.row
activeobject.name

etc...

...but I can't seem to do this in VBA can anyone help?

Thanks

Rich

ActiveControl, ActiveForm, ActiveReport, and ActiveDatasheet, among
others, are properties of the Screen object. See the help file for
details.

Active control name and value:
Screen.ActiveControl.Name
Screen.ActiveControl.Value

Active form name:
Screen.ActiveForm.Name

Active report name:
Screen.ActiveReport.Name

Note that referring to these properties will raise an error if there is
no active control/form/report, etc. If that's a possibility in the
context in which you make the reference, you have to trap that error.

 

[Rich Wills via AccessMonster.com]

Thanks Dirk. I really wanted the control to be pointing to a Label so when I
clicked on the label it would set a string to include the Name of the label
but it doesn't seem to work as a label cannot be "Active", that I can find.
eg

Private Sub CTRef_Click()

Dim MyName As String
MyName = Screen.ActiveControl.Name
Me.CCTREFInput = MyName
'=====other things

End Sub

It will later refer to a control named the same but this is set as "Enabled =
false" as I don't want the user to be able to change or highlight the text
within it. So the "On-Click" event on the textbox would not be the solution.
I think this makes sense!?!?!?

If you have any ideas would really be appreciated!

Thanks for your help

Rich

It seem simple but can't work out how to refer to the "Current" or
"Active" field/control etc within VBA. Is there a way of doing this?

[quoted text clipped - 12 lines]

Rich

ActiveControl, ActiveForm, ActiveReport, and ActiveDatasheet, among
others, are properties of the Screen object. See the help file for
details.

Active control name and value:
Screen.ActiveControl.Name
Screen.ActiveControl.Value

Active form name:
Screen.ActiveForm.Name

Active report name:
Screen.ActiveReport.Name

Note that referring to these properties will raise an error if there is
no active control/form/report, etc. If that's a possibility in the
context in which you make the reference, you have to trap that error.

 

[Dirk Goldgar]

Thanks Dirk. I really wanted the control to be pointing to a Label so
when I clicked on the label it would set a string to include the Name
of the label but it doesn't seem to work as a label cannot be
"Active", that I can find. eg

Private Sub CTRef_Click()

Dim MyName As String
MyName = Screen.ActiveControl.Name
Me.CCTREFInput = MyName
'=====other things

End Sub

It will later refer to a control named the same but this is set as
"Enabled = false" as I don't want the user to be able to change or
highlight the text within it. So the "On-Click" event on the textbox
would not be the solution. I think this makes sense!?!?!?

If you have any ideas would really be appreciated!

You're right that a label can't receive the focus, so it can't be the
active control. But I'm not entorely clear on what you're trying to
accomplish. Clearly, in an event procedure for the label's Click event
(e.g., "CTRef_Click()"), you know the name of the label that was
clicked. So you can easily hard-code that.

On the other hand, if you're trying to develop a general-purpose routine
to handle the Click event for multiple label, so you're trying to avoid
hard-coding the name, I can't think of any property or object that will
give you that. What you could do is set up a function that would
receive the name as a string parameter, and then set each label's
OnClick event property to a function expression that calls the function
and passes the relevant name.

For example, the function could look like this:

'----- start of code -----
Function SetCCTREFInput(pstrName As String)

Me.CCTREFInput = pstrName

'=====other things

End Function
'----- end of code -----

And then you'd set the CTREF label's OnClick event property (not
creating an event procedure at all) to this:

=SetCCTREFInput("CTREF")

If you have another label named "OTHER", related to another field, you'd
set that label's OnClick property to:

=SetCCTREFInput("OTHER")

As I said, I'm not sure I understand what you're trying to do, but maybe
this workaround will get you there.

 

[Rich via AccessMonster.com]

Hi Dirk

That is a slightly shorter way for me so thank you!

The main object of what I was trying to accomplish was when a user clicks on
one of the 50 or so LABELS it would return a procedure or function. Each
[label.name] is the same "Name" as the control source of a field in one of my
tables. I wanted the function to open a seperate form (Has just a textbox)
and to set the textbox controlsource to the Name of the label that the user
clicks on.

I din't want to have to write out a piece of code for each individual Label
hence my question to identify which LABEL was clicked on within a function.

Your example will shorten the code but I will still have to type each LABEL
"Name" in the procedure.

If you have any further ideas would love to know but will use your function
in the meantime. Thanks Dirk


Rich

Thanks Dirk. I really wanted the control to be pointing to a Label so
when I clicked on the label it would set a string to include the Name

[quoted text clipped - 16 lines]

If you have any ideas would really be appreciated!

You're right that a label can't receive the focus, so it can't be the
active control. But I'm not entorely clear on what you're trying to
accomplish. Clearly, in an event procedure for the label's Click event
(e.g., "CTRef_Click()"), you know the name of the label that was
clicked. So you can easily hard-code that.

On the other hand, if you're trying to develop a general-purpose routine
to handle the Click event for multiple label, so you're trying to avoid
hard-coding the name, I can't think of any property or object that will
give you that. What you could do is set up a function that would
receive the name as a string parameter, and then set each label's
OnClick event property to a function expression that calls the function
and passes the relevant name.

For example, the function could look like this:

'----- start of code -----
Function SetCCTREFInput(pstrName As String)

Me.CCTREFInput = pstrName

'=====other things

End Function
'----- end of code -----

And then you'd set the CTREF label's OnClick event property (not
creating an event procedure at all) to this:

=SetCCTREFInput("CTREF")

If you have another label named "OTHER", related to another field, you'd
set that label's OnClick property to:

=SetCCTREFInput("OTHER")

As I said, I'm not sure I understand what you're trying to do, but maybe
this workaround will get you there.

 

[Dirk Goldgar]

Hi Dirk

That is a slightly shorter way for me so thank you!

The main object of what I was trying to accomplish was when a user
clicks on one of the 50 or so LABELS it would return a procedure or
function. Each [label.name] is the same "Name" as the control source
of a field in one of my tables. I wanted the function to open a
seperate form (Has just a textbox) and to set the textbox
controlsource to the Name of the label that the user clicks on.

I din't want to have to write out a piece of code for each individual
Label hence my question to identify which LABEL was clicked on within
a function.

Your example will shorten the code but I will still have to type each
LABEL "Name" in the procedure.

If you have any further ideas would love to know but will use your
function in the meantime. Thanks Dirk

I guess one possibility would be to put a transparent command button
over each label, name the buttons according to a scheme that allows you
to extract the field name in question, and then use something like your
original code. The transparent command buttons *will* get the focus,
and so Screen.ActiveControl.Name will return the name of the button.