Using tab setting in a text box

Question

What is the correct syntax for copying data contained in cells into a text box and using a tab setting.

eg

Data in cells looks like

Name    Gender   Reference 1    Reference 2

John          M              26                    A

Mary          F               40                    C

etc

Textbox 1 looks like

Name :	John
Gender :	M
Reference 1 :	26
Reference 2 :	A

Textbox 2 looks similar

Thanks in advance

Answer 1

I think "under pressure" is looking to populate a textbox shape that you would add to a worksheet, not an activeX or some kind of textbox on a UserForm.

Have a good holiday!

Thank you, we have fantastic weather, warm and sunny, I will enjoy it.

In additional to the other thread I extend my code a little to create the ActiveX text boxes automatically.

So the OP can choose what he like.

Andreas.

Sub Test()

  Dim All As Range

  Dim Header As Range, Data As Range, Where As Range

  Dim i As Long, l As Long, r As Long

  Dim S As String

  Dim O As OLEObject

  Dim MyTextBox As Object

  'Where is the data?

  Set All = Range("A1").CurrentRegion

  'Get the headings

  Set Header = All.Rows(1)

  'Get the max. length

  For i = 1 To Header.Cells.Count

    If Len(Header.Cells(i)) + 2 > l Then l = Len(Header.Cells(i)) + 2

  Next

  'The place for the first text box

  Set Where = All.Offset(, All.Columns.Count + 1).Resize(5, 2)

  'Visit each row

  For r = 2 To All.Rows.Count

    'Get the data row

    Set Data = All.Rows(r)

    S = ""

    For i = 1 To Header.Cells.Count

      S = S & Left(Header.Cells(i) & " :" & Space(l), l) & vbTab & Data.Cells(i) & vbCrLf

    Next

    With Where

      'Add the ActiveX text box

      Set O = Me.OLEObjects.Add("Forms.TextBox.1", _

        Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)

      Set MyTextBox = O.Object

      With MyTextBox

        'Setup the properties

        .MultiLine = True

        .TabKeyBehavior = True

        'Store the string

        .Value = S

        'Let it shrink

        .AutoSize = True

      End With

      'Move the next text box below this one

      Set Where = Where.Offset(O.BottomRightCell.Row - Where.Row + 1)

    End With

  Next

End Sub

Answer 2

a) The text box properties MultiLine and TabKeyBehavior must be True.

b) The length of the words in the left column (in the textbox) must have the same length. We can accomplish that by adding spaces.

c) Build a string with the padded words, then add a TAB char, then the next word, then CR and LF for the line break.

Andreas.

PS.: I'm on vacation next 2 weeks, so an answer can take a while if you have furthermore questions.

Sub Test()

  Dim All As Range

  Dim Header As Range, Data As Range

  Dim i As Integer, l As Integer

  Dim S As String

  Set All = Range("A1").CurrentRegion

  Set Header = All.Rows(1)

  Set Data = All.Rows(2)

  For i = 1 To Header.Cells.Count

    If Len(Header.Cells(i)) + 2 > l Then l = Len(Header.Cells(i)) + 2

  Next

  For i = 1 To Header.Cells.Count

    S = S & Left(Header.Cells(i) & " :" & Space(l), l) & vbTab & Data.Cells(i) & vbCrLf

  Next

  Me.TextBox1 = S

End Sub

Answer 3

Andreas

Thanks for your reply and, like Rich says, you are having a well-earned rest on holiday.

What amazes me about VBA, and I am fairly new at this, is that there are many ways to get to the solution but the bit that actually does the job is generally the same.  So Rich's solution to what I wanted, on first appearance, looks nothing like what you have, but at the heart of his solution is a version of what you have here :-

  S = S & Left(Header.Cells(i) & " :" & Space(l), l) & vbTab & Data.Cells(i) & vbCrLf

Thanks again

Answer 4

a) The text box properties MultiLine and TabKeyBehavior must be True.

Hi Andreas,

I think "under pressure" is looking to populate a textbox shape that you would add to a worksheet, not an activeX or some kind of textbox on a UserForm.

Some more background here: http://answers.microsoft.com/en-us/office/forum/office_2010-customize/adding-text-to-a-text-box-using-a-macro/30766851-cd49-4b1d-aa32-728b8e7b8571#LastReply

Have a good holiday!

Cheers

Rich