Simple One--Copy data from a cell, create text box and paste

[MrsTFB]

Hey guys,
I am so frustrated to be here again, I should be able to figure some of this on my own. What I have is a form in VBA that the engineers enter information into. I have the cells set to wrap text, but sometimes that is not cosmetically the answer. What I want to do is when the sentence is too long, create a text box and place the data in say ("d22&quot into the text box. It also should be placed about the area of d22. What do you guys think?

Thanks for any ideas! You guys are wonders!

Mrstfb in tennessee

 

[bryanbayfield]

Call the text box

txtentry

Private Sub txtentry_exit()
dim overflw_r as range
set overflw_r = range("d22")
if len(txtentry.value) > [i]Your_number[/i] then
     overflw_r.value = txtentry.value
     txtentry.value = "d22"
     end if
end sub

This is very basic but should get you started. You will need something more sophisticated if you are moving between sheets etc. The exit event occurs just before another control on teh form receives focus from another user.

Hope this helps. SuperBry!

 

[bryanbayfield]

Ooops, I've got that completely round the wrong way, haven't I?

I do apologise, work has turned my brain to mush.

Humble apologies. SuperBry!

 

[MrsTFB]

Something must have turned my brain to mush also, because I do not understand the oops! What part is round the wrong way? I've never used overflw_r.

HELP!!

mrstfb in tennessee

 

[bryanbayfield]

I assumed you wanted data that is too big to go into a text box to be stored somewhere in a worksheet whereas you want information too large to go into a worksheet cell to be pasted into a text box that would sit on top of the cell. Don't you?!

overflw_r is just a name that I gave to a cell. It's not a name in ActiveBasic, it's user-defined. SuperBry!

 

[MrsTFB]

You were correct, that I want data when too large to be cosmetically right for a wrap text to be copied and pasted into a text box. The text boxes cannot be named by number because they will change each time.

Any ideas!

mrsTFB in Tennessee

 

[bryanbayfield]

This should do the job:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Integer
n = 10
Target.ClearComments
If Len(Target.Value) > n Then: Target.AddComment Target.Value
End Sub

Phew! Instead of a text box it uses a comment box. I've used a cut-off of ten characters as an example, but you could use what you wanted up to c. 35,000. SuperBry!