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]

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.