Accessing Mutiple TextBoxes

[TimBro]

Thankyou for the code Peter, i´ve added the Part which reads out th
Characters and addes them to the string. The export now word fin
(entire text is transferd) but the carriage returns go missing an
spaces are represented as "squares". Any solution to this ?

General question: You have helped with the TextBoxes from the drawin
menu. When using the TextBox Control from the controls menu the entir
text including the formating is transfer automatically into my ACCESS 9
Memo field. If I could address the TextBox controls then these proble
won´t occur. My Textbox Contols are placed directly on the Workshee
and not in a UserForm therfore I haven´t been able to address them.
In which container Object are they included ?

Thanx for all your help !
Ti

 

[Peter T]

Hi Tim

Thankyou for the code Peter, i´ve added the Part which
reads out the Characters and addes them to the string.
The export now word fine (entire text is transferd) but
the carriage returns go missing and spaces are
represented as "squares". Any solution to this ?

In my testing (Excel only) the combination vbcr & cblf
returned proper paragraphs with a line spaced between. If
you are programmatically writing text later to be copied
to another App, try this:
SomeText & chr(13) & chr(10) & moreText

General question: You have helped with the TextBoxes
from the drawing menu. When using the TextBox Control
from the controls menu the entire text including the
formating is transfer automatically into my ACCESS 97
Memo field. If I could address the TextBox controls then
these problem won´t occur. My Textbox Contols are placed
directly on the Worksheet and not in a UserForm therfore
haven´t been able to address them.
In which container Object are they included ?

To return text from a controls textbox try this:

Sub test4()
Dim sName As String, MyText As String
Dim ws As Worksheet
sName = "TextBox1"
Set ws = ActiveSheet
' with a controls textbox named "TextBox1" in the
' names dropdown (left of inputbar)

MyText = ws.OLEObjects(sName).Object.Text
Debug.Print MyText
End Sub

I recall in your first post you were returning text this
way:
Text = Worksheets(TableName).Comment.Text

I assume you had renamed the CodeName of your textbox
as "Comment" in Properties. This is independently named to
it's OLEObject name, that you see in the dropdown Names
list to left of the input bar (in design mode)

I don't know how to directly refer to an OLEObject by its
Codename other than a long winded method something like
this:

Sub test5()
Dim obj As OLEObject
Dim oTB As MSForms.TextBox
Dim MyText As String
Dim ws As Worksheet
Set ws = ActiveSheet
'with a controls Texbox named "Comment" in Properties
For Each obj In ws.OLEObjects
If TypeOf obj.Object Is MSForms.TextBox Then
Set oTB = obj.Object
If oTB.Name = "Comment" Then
MyText = oTB.Text
MsgBox MyText
Exit For
End If
End If
Next
End Sub

Above is even more long winded than needs be to
demonstrate MSForms.TextBox. But if anyone knows a more
direct method to refer to an OLEObject's CodeName I'd also
be interested.

Regards,
Peter