ActiveControl Property of OLEObject?

[PS]

Is there any property to OLEObject in a worksheet
like ActiveControl property in userform?
If not, is there any trick to that?

thanks.

[John Green]

If a control is selected, Selection will return a reference to it.

If you are trying to get a reference to the control that executed a macro,
you can use Application.Caller in the macro to return the name of the
object.

Just some thoughts,

--

John Green
Sydney
Australia

"PS" <psk...@hotmail.com> wrote in message
news:74c101c1ed86$3182a290$b1e62ecf@tkmsftngxa04...

[PS]

Thank you for your quick reply.
With your advice, I made an event procedure.
I have 3 textboxes in a worksheet.
And I want to display the name of active textbox
in the textbox3.

'-------------------------------------------
Private Sub TextBox1_GotFocus()
If TypeOf Selection Is TextBox Then
TextBox3.Value = Selection.Object.Name
End If
End Sub
'--------------------------------------------

But this is not working. how can I acquire active
textbox's name

thanks.

[PS]

Dear John.
Maybe you are the writer of "Excel 2002 VBA"?
I always read your book, and always thank you.
I'm very surprised to receive your reply directly.
Thanks again.

[John Green]

You could accomplish this with:

Private Sub TextBox1_GotFocus()
TextBox3.Value = TextBox1.Name
End If
End Sub

However, I assume you want generalised code that can be common to a number
of textboxes. The only way I know to accomplish this is to use a class
module with event trapping. However, you can't trap all events this way,
including the GotFocus event. You can trap the Change and MouseDown events.
We can give you more details if this sound promising.

Otherwise, it is probably better if you explain what you want to do,

--

John Green
Sydney
Australia

"PS" <psk...@hotmail.com> wrote in message

news:327101c1ed92$ba117940$3aef2ecf@TKMSFTNGXA09...

[John Green]

Thank you for buying the book<g>

--

John Green
Sydney
Australia

"PS" <psk...@hotmail.com> wrote in message

news:2b9e01c1ed96$142cc440$9ae62ecf@tkmsftngxa02...

[PS]

>You can trap the Change and MouseDown events.
>We can give you more details if this sound promising.

To be frank with you, I'm still a beginner to Excel VBA.
Would you please teach me some?

[John Green]

<g> Just explain what you want to accomplish. We can take it from there,

--

John Green
Sydney
Australia

"PS" <psk...@hotmail.com> wrote in message

news:2e2501c1edcf$76c41660$19ef2ecf@tkmsftngxa01...

[PS]

><g> Just explain what you want to accomplish. We can take
it from there,

Thanks.
I have 8 TextBoxes in a worksheet.
7 for input, 1 for monitoring active TextBox's name.
And when I input any text in 1st to 7th TextBox,
I want the 8th TextBox show active TextBox's name.
Teach me, please.
Many thanks.

[Tom Ogilvy]

right click on the worksheet tab and select view code.

Paste in

Private Sub TextBox1_GotFocus()
TextBox8.Value = TextBox1.Name
End Sub

Copy it and paste it 6 times. Edit the 1 and make it reflect the name of
each of the textboxes.

Private Sub TextBox2_GotFocus()
TextBox8.Value = TextBox2.Name
End Sub

Private Sub TextBox3_GotFocus()
TextBox8.Value = TextBox3.Name
End Sub

Private Sub TextBox4_GotFocus()
TextBox8.Value = TextBox4.Name
End Sub

etc.

I don't see any particular reason to get fancier than this.

Regards,
Tom Ogilvy

PS <psk...@hotmail.com> wrote in message

news:6fdf01c1ede6$8d9ecc60$9ee62ecf@tkmsftngxa05...

[John Green]

Tom's solution is the simplest when not too many textboxes are involved.

For contrast, try the following code that uses a class module to encapsulate
the event procedure and places the references to the objects into a
collection. I have used the Change event because the more desirable GotFocus
event is not available in a class module.

Insert a class module (Insert|Class Module in the VB Editor menu)

Type the following statements at the top of the class module:

--------------------------------------------------
Option Explicit

Public WithEvents tb As MSForms.TextBox
---------------------------------------------------

You can now select tb from the top left hand drop down at the top of the
class module. The first and last lines of the following event procedure will
be inserted and you can fill in the line of code between:

---------------------------------------
Private Sub tb_Change()
Sheet1.TextBox8.Text = tb.Name
End Sub
-----------------------------------------

You can now insert a standard module and type in the following code:

-------------------------------------------
Option Explicit

Dim tbX As Class1
Dim colTextBoxes As New Collection

Private Sub SetUp()
Dim ole As OLEObject

For Each ole In Sheet1.OLEObjects
If TypeOf ole.Object Is MSForms.TextBox _
And ole.Name <> "TextBox8" Then
Set tbX = New Class1
Set tbX.tb = ole.Object
colTextBoxes.Add tbX
End If
Next ole

End Sub
---------------------------------------------------

Execute the SetUp macro and you will get the desired result.

You can run SetUp from the WorkBook_Open event procedure if you want to have
the code assigned automatically,

--

John Green
Sydney
Australia

"PS" <psk...@hotmail.com> wrote in message

news:6fdf01c1ede6$8d9ecc60$9ee62ecf@tkmsftngxa05...

[PS]

Dear John, and Tom

Thanks to your advice, I can proceed my work.
Thanks. Maybe, I'm going to ask some questions
again, and that time I will wait for you advice.
Thank you very much.