How To align (set tabs or columns) on a text string

[eoaapp]

Hello
The following code
Quote
Set AADrpDn = .Shapes.AddFormControl(xlDropDown, 4, 84 + 33, 536, 21)
For i = 1 To 10
TextF = Cells(i, 2) & Cells(i, 3) & Cells(i, 4) & Cells(i, 5) & Cells(i, 6)
AADrpDn.ControlFormat.AddItem TextF
Next i
Unquote
is in a excel macro, which working as follows:

1st Create a drop down list

2nd Read five (5) cells in ten (10) rows,

3rd Create a string for each row

4th Add the string, as item to the drop down list.
The drop down list looks (aligned) as follows
a1a1a1a1_a2a2a2a2_a3a3a3a3_a4a4a4_a5a5a5a5 (The first row)
b1b1_b2b2b2_b3b3b3b3b_b4b4b4b4b_b5b5 (The second row)


Here I need help (key words) to make the drop down list to look (to be aligned) as follows (lets say in columns):
a1a1a1a1__a2a2a2a2__a3a3a3a3_____a4a4a4______a5a5a5a5"
b1b1______b2b2b2____b3b3b3b3b____b4b4b4b4b___b5b5"

note that _ equals to space


Many thanks
regards

 

[CautionMP]

eoaapp,
One possible approach below. Works in Excel 2k SP1 on WinXP.

Sub JustifyTextToImmediate()
  Dim intRow As Integer, intColumn As Integer, intPad As Integer
  Dim astrParts(1 To 5) As String
  
  'This is how wide each "column" will be
  intPad = 10
  For intRow = 1 To 5
    For intColumn = 1 To 5
      'Pad the string to the length wanted
      astrParts(intColumn) = String(intPad, " ")
      'LSet should right the left side and respect the total length
      LSet astrParts(intColumn) = ActiveSheet.Cells(intRow, intColumn)
    Next intColumn
    'Create a single string from the parts
    Debug.Print Join(astrParts)
  Next intRow
End Sub

Keep in mind that you will need to use a non-proportional font on [tt]AADrpDn[/tt] for the text to render in columns.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)