Expand cells

[Ulf Nilsson]

Hi,

I want to add some extra spaces after text in a cell so
the cell expands. The following does not work:

Selection.Typetext " "

or

Selection.InsertSymbol Font:="Garamond", _
CharacterNumber:=32, Unicode:= _
True
Selection.InsertSymbol Font:="Garamond", _
CharacterNumber:=32, Unicode:= _
True
Selection.InsertSymbol Font:="Garamond", _
CharacterNumber:=32, Unicode:= _
True

The code produces thre extra spaces, but the cell does
not expand. If I type extra letters, for example
Selection.TypeText "123", then the cell expands.

/ Ulf

 

[Klaus Linke]

Hi Ulf,

You could use non-breaking spaces ChrW(&HA0), but it would likely be
"cleaner" to set the column width properly.

Regards,
Klaus

 

[Ulf Nilsson]

Hi Klaus,
Thnaks for your solution. I tried to record using non-
breaking spaces, but the macro recorded only " ". I
will use ChrW(&HAO) for now, and will try to make
it "cleaner" when time allows.

/ Ulf

 

[Jezebel]

Don't try to create macros using the recorder. It's only worthwhile purpose
is to give you a hint as to what the macro code should look like ... but the
code it produces is uniformly AWFUL.

 

[Klaus Linke]

Don't try to create macros using the recorder. [...]

In fact, it does work in the current case.

The only problem is that the VBA editor can't display formatting
characters.
It shows
Selection.TypeText = " "
but actually inserts a non-breaking space.

The macro recorder uses the characters themselves if they are in the
Windows code page (1252 usually), and ChrW(####) if they aren't.
The non-breaking space is in code page 1252.

Things will hopefully get simpler once the VBA editor uses Unicode...

Regards,
Klaus

 

[Jezebel]

Better still, the OP might explain the point of the exercise ... since
adding spaces to the end of a cell doesn't, in itself, make any difference
at all to the appearance of the document ... as you suggested earlier,
setting the column width is probably the intention.

Don't try to create macros using the recorder. [...]

In fact, it does work in the current case.

The only problem is that the VBA editor can't display formatting
characters.
It shows
Selection.TypeText = " "
but actually inserts a non-breaking space.

The macro recorder uses the characters themselves if they are in the
Windows code page (1252 usually), and ChrW(####) if they aren't.
The non-breaking space is in code page 1252.

Things will hopefully get simpler once the VBA editor uses Unicode...

Regards,
Klaus