Hi,

I have a string of text in a text box and I want to allow the user to select
values from a list box beside the text box and dependent on where the cursor
was last positioned in the text box before the selection of the item in the
list box....I want to insert the value selected from the list box...

Can someone suggest an approached ?

I have found something along the lines of what i need but only for Access
VBA...it does not seem to work with the same properties in Excel VBA
http://www.everythingaccess.com/tuto...ursor-position

You are going to kick yourself when you find out how easy what you asked is
to do. First off, though, I would suggest you insert the text into the
TextBox in response to a double click in the ListBox rather than a single
click; that way the user won't be able to accidentally click in the ListBox
and add text to the TextBox unknowingly. Assuming you agree, here is the
ListBox's DblClick event code that will do what you asked (if you disagree,
just put the code into the ListBox's Click event instead)...

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.SelText = ListBox1.Text
End Sub

That is it; see... I told you that you would want to kick yourself over how
easy this is to do.<g By the way, obviously change the example control
names I used to match the actual names you have given them. Also, for the
user's convenience, set the TextBox's HideSelection property to False so
he/she can see where the text will end up going.

By the way, since you didn't say, I had to guess... I set this up with the
controls on a UserForm, but it should be modifiable for use with ActiveX
controls (from the Controls Toolbar) placed directly on the worksheet.

Rick


"scott56hannah" wrote in message
...
Hi,

I have a string of text in a text box and I want to allow the user to
select
values from a list box beside the text box and dependent on where the
cursor
was last positioned in the text box before the selection of the item in
the
list box....I want to insert the value selected from the list box...

Can someone suggest an approached ?

I have found something along the lines of what i need but only for Access
VBA...it does not seem to work with the same properties in Excel VBA
http://www.everythingaccess.com/tuto...ursor-position

Rick,

Thanks for that....it worked well...and maybe I should kick myself

Scott

"Rick Rothstein (MVP - VB)" wrote:

You are going to kick yourself when you find out how easy what you asked is
to do. First off, though, I would suggest you insert the text into the
TextBox in response to a double click in the ListBox rather than a single
click; that way the user won't be able to accidentally click in the ListBox
and add text to the TextBox unknowingly. Assuming you agree, here is the
ListBox's DblClick event code that will do what you asked (if you disagree,
just put the code into the ListBox's Click event instead)...

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.SelText = ListBox1.Text
End Sub

That is it; see... I told you that you would want to kick yourself over how
easy this is to do.<g By the way, obviously change the example control
names I used to match the actual names you have given them. Also, for the
user's convenience, set the TextBox's HideSelection property to False so
he/she can see where the text will end up going.

By the way, since you didn't say, I had to guess... I set this up with the
controls on a UserForm, but it should be modifiable for use with ActiveX
controls (from the Controls Toolbar) placed directly on the worksheet.

Rick


"scott56hannah" wrote in message
...
Hi,

I have a string of text in a text box and I want to allow the user to
select
values from a list box beside the text box and dependent on where the
cursor
was last positioned in the text box before the selection of the item in
the
list box....I want to insert the value selected from the list box...

Can someone suggest an approached ?

I have found something along the lines of what i need but only for Access
VBA...it does not seem to work with the same properties in Excel VBA
http://www.everythingaccess.com/tuto...ursor-position