I have a textbox created from the forms toolbar, floating on a worksheet
(it's not in a form).
I want to be able to enter text in this box, and then, when I change the
focus back to the sheet (or, better, press TAB), a macro is launched.
I am able to Assign a Macro to the textbox, but when I click the textbox
(to enter text), the macro launches. Now I have to rightclick to select
the text box and enter text. Not ideal.

Is there some way of capturing the shift in focus from the textbox to the
worksheet in a worksheet event? Or is there another way?

I know I can do this with a Controls Textbox, but I'd like to stick with
the Forms one if I can (I have other stuff based on it).

Is what I'm seeking possible?
Thanks in advance,
------------------
Darren

I don't think that there's a way to determine where you just came from.

Maybe it would be simpler to just add a small button next to the textbox that
can be clicked on to run the macro.



Darren Hill wrote:

I have a textbox created from the forms toolbar, floating on a worksheet
(it's not in a form).
I want to be able to enter text in this box, and then, when I change the
focus back to the sheet (or, better, press TAB), a macro is launched.
I am able to Assign a Macro to the textbox, but when I click the textbox
(to enter text), the macro launches. Now I have to rightclick to select
the text box and enter text. Not ideal.

Is there some way of capturing the shift in focus from the textbox to the
worksheet in a worksheet event? Or is there another way?

I know I can do this with a Controls Textbox, but I'd like to stick with
the Forms one if I can (I have other stuff based on it).

Is what I'm seeking possible?
Thanks in advance,
------------------
Darren

--

Dave Peterson

On Mon, 11 Jul 2005 21:29:17 +0100, Dave Peterson
wrote:

I don't think that there's a way to determine where you just came from.

Maybe it would be simpler to just add a small button next to the textbox
that
can be clicked on to run the macro.

Aha, ingenious :)
The simple solutions are the best.

------------------
Darren

Dave,

use the textbox from the Control Toolbox, not the Forms toolbox, but
you'll see a lot more events and possibilities (use the LostFocus event
for your macro).

Dm Unseen

But the OP wrote this:

I know I can do this with a Controls Textbox, but I'd like to stick with
the Forms one if I can (I have other stuff based on it).

(And I'm pretty sure that he meant Drawing toolbar, though.)

DM Unseen wrote:

Dave,

use the textbox from the Control Toolbox, not the Forms toolbox, but
you'll see a lot more events and possibilities (use the LostFocus event
for your macro).

Dm Unseen

--

Dave Peterson

OK, dind't read carefully enough:)

Maybe an idea is to link the control to a cell and use the SheetChange
event on that cell. less pretty but still workable.

Dm Unseen

On Tue, 12 Jul 2005 14:05:59 +0100, Dave Peterson
wrote:

But the OP wrote this:

I know I can do this with a Controls Textbox, but I'd like to stick with
the Forms one if I can (I have other stuff based on it).

(And I'm pretty sure that he meant Drawing toolbar, though.)

Oh yeah, you're right.

Darren

On Tue, 12 Jul 2005 14:22:24 +0100, DM Unseen wrote:

OK, dind't read carefully enough:)

Maybe an idea is to link the control to a cell and use the SheetChange
event on that cell. less pretty but still workable.

The text box is transparent and floating on top of a picture (which
contains a visual representation of a box), which is why I can't use a
cell.

Darren