Maintaining Formatting in a UserForm

[Rawce]

Hello All,

This week's problem has been fed back to me by the end users of my
Excel app (spreadsheet controlled by userforms initialised by
commandbar controls). Users can either use the form for data input
(changes to cell values are saved when the user hits 'Save', quits the
form or moves down a line) or make changes in Excel itself and maybe
bring up the form for some of the more advanced functions.

When in Excel itself, they can apply formatting to all or part of the
text (using the standard Format > Cells > etc.). When this gets pulled
into the form, the formatting is stripped out and things such as super
and sub-scripts are lost or Bold, Italics, etc. are applied to the
whole cell when saving.

Is there anyway to achieve either of the following:
1. Retain the formatting from the cell and pull it into the form's
textbox?
2. Retain the formatting from the cell when "saving" the form values
back to the spreadsheet?

Please note, "saving" is simply done by assigning the current contents
of the form's textbox (e.g. InputForm.TextBox1.Value) to the applicable
cell range.

Cheers for any help you can offer,

Ross.

 

[Peter T]

Is there anyway to achieve either of the following:

1. Retain the formatting from the cell and pull it into the form's
textbox?

You could apply the cell's Fill colour to the textbox background and most of
the font attributes.

2. Retain the formatting from the cell when "saving" the form values
back to the spreadsheet?

There are some 30+ individual cell formats that you could obtain from the
original cell and apply to the new cell. Alternatively paste-special formats
to a hidden cell in your addin (?) and re-apply back.

Regards,
Peter T

 

[Rawce]

"You could apply the cell's Fill colour to the textbox background and
most of the font attributes."

Good thinking, I handn't thought of that. Could be useful as some of my
title cells are highlighted to indicate that they are titles and this
will make it stand out more in the form.

"There are some 30+ individual cell formats that you could obtain from
the original cell and apply to the new cell. Alternatively
paste-special formats to a hidden cell in your addin (?) and re-apply
back."

Unfortunately, the main reason I need this functionality is to retain
the sub and superscript which won't work with format pasting. I was
hoping that there would be some kind of character reference that I
could do a search on when loading the data into the form and when
saving it back to the cell, but it seems there isn't. When saving it
back I could use CHR(178) and CHR(179) as I'll mainly be using squares
and cubes, but I've no obvious way of searching for the ² and ³ in
the first place.

 

[Rawce]

Oh, and thanks Peter for your help!

You could apply the cell's Fill colour to the textbox background and most of
the font attributes.


There are some 30+ individual cell formats that you could obtain from the
original cell and apply to the new cell. Alternatively paste-special formats
to a hidden cell in your addin (?) and re-apply back.

Regards,
Peter T

 

[Rawce]

Just discovered that using ALT+0178 (hold the ALT key then type in 0,
1, 7 and 8 on the numberpad only - needs NUM LOCK on) does work as it's
a specific character (equivalent of CHR(178)) and therefore not a
format on a 2. This means that for the majority of the time when I'm
using squares and cubes (e.g. m³ for metres cubed) I shouldn't have a
problem so long as my end users use the ALT key method of input.

As for other scientific units like flow rates (metres cubed per second
or hour) I can't really do much unless I use m³/s instead of
subscripting a -1 after the s. Guess this is the best I can achieve,
but will suffice for the majority of the time.

Cheers,

Ross.

 

[Peter T]

Indeed replacing mixed characters.font formats is problematic and could
involve a lot of tedious work, check out 'Characters in Help.

When returning potentially mixed characters to a variable ensure it's
declared as Variant, eg

Dim v As Variant
v = cell.font.superscript

if IsNull(v) then ' mixed
loop each character

However perhaps you could use a bunch of search & replace for you potential
units, eg

s = "abc m2"
s = Replace(s, " m2", " m­²")

(Replace n/a in xl97)

Or,

n = instr(cell, " m2")
if n then
cell.Characters(n + 2, 1).Font.Superscript = True

Regards,
Peter T

Just discovered that using ALT+0178 (hold the ALT key then type in 0,
1, 7 and 8 on the numberpad only - needs NUM LOCK on) does work as it's
a specific character (equivalent of CHR(178)) and therefore not a
format on a 2. This means that for the majority of the time when I'm
using squares and cubes (e.g. m³ for metres cubed) I shouldn't have a
problem so long as my end users use the ALT key method of input.

As for other scientific units like flow rates (metres cubed per second
or hour) I can't really do much unless I use m³/s instead of
subscripting a -1 after the s. Guess this is the best I can achieve,
but will suffice for the majority of the time.

Cheers,

Ross.

 

[Rawce]

Good suggestions, thanks. I've told everyone to use the ALT character
input method, knowing full well they won't be bothered and will just
use m2, etc. instead, so maybe I will force it using your replace
method above.

Cheers,

Ross.