Simulating tab stops in Excel cells

[Walter Briscoe]

My VBA is Microsoft Visual Basic 6.3, from Excel 2003 (11.6535.6360) SP1
from XP Professional Version 2002 SP3.

Google groups tells me cells do not sensibly display tab characters.
I have some cells where each cell contains a small set of information.
I want vertical alignment of such information.
An example of such data is:
29 Mar 09 29 Mar 09 Central Liverpool Street to Leytonstone
04 Apr 09 05 Apr 09 Hammersmith & City Edgware Road to Hammersmith.

If I use a fixed pitch font, such as Courier New, I can do something
like Left(LineName & Spaces, Len("Hammersmith & City")) to pad the third
pseudo-column to a fixed width.
I am actually using Tahoma, which is a proportional font; that technique
fails to deliver the desired result for proportional fonts.

I have a small number of distinct values for the third column;
I could roughly bludgeon something by padding from an array of spaces;
I am looking for something more elegant.

I have looked for terms like format and align in help, without success.

(Solutions using multiple columns do not fit my criteria. ;( )
(This posting will be distorted, if read in a proportional font

 

[Peter T]

(Solutions using multiple columns do not fit my criteria. ;( )

I can only suggest you modify your self imposed constraints. Looks like you
have 4 or 5 fields so why not dump your data accordingly, Excel is good for
things like this!

Date1, Date2, Route, From, To

You can always "Join" your data for display elsewhere in Excel, say in a
Textbox which does accept tabs, or for export. Programmatically include a
vbTab or manually in a textbox Ctrl-tab.

The other difficulty you will have is knowing how many tabs to insert, so
you may still require some way of determining the overall length of the left
portion. Briefly, in Excel there are two ways to get the length of even
mixed sized fonts:
- Temporarily place the text in a cell on a dummy sheet and Autosize the
column.
- Similar approach with an Autosize textbox (lot more code but more
accurate).

Regards,
Peter T