Interactive user-form

**Un-Do Re-Do** · 12-18-2017, 03:32 AM

The following code is based on algorithm by another forum member.
The user-form shows the strings in selected cell/s, and then allows the font of each character to be changed. Blank cells are bypassed.

I'm trying to add another feature, which is to allow editing of the form and transferring values to cells (see "Private Sub EditAdd" called up using "EDIT" button).

There are two issues:
(1) Selection with blank cells gives an error.
(2) Running the code also changes the font unintentionally.

How can this be fixed?

Snapshot.png

Please Login or Register  to view this content.

**Keebellah** · 12-18-2017, 04:14 AM

Hi, I haven't downloaded your file just read (part) of the code but with the EditAdd you are processing for each ....

Well, the Each cell will be processed.
I'll download your file and see if I can figure out what you are trying to achieve and if I have the time I'll work on it.

**Andy Pope** · 12-19-2017, 11:47 AM

You are not consitently using the IsEmpty when checking each cell within the selection.

You need to do it in the same way as the Initialize code.

**LJMetzger** · 12-19-2017, 01:34 PM

Hi Excel_&_Help,

See the attached file containing the following code. It is almost a complete rewrite and redesign of what you originally had,but hopefully it is a lot easier to follow. I left your code as is, and added UserForm2. In the UserForm2 code module:

Please Login or Register  to view this content.

Lewis

**Un-Do Re-Do** · 12-20-2017, 01:22 AM

Thanks for the assistance.
With the solution at #4, the change in font made changes to all selected cells. This was supposed to be only for selected characters within the textbox.
Modification to code at #4 now has this function.
* There is one thing missing which I don't know will be possible. When the EDIT is made (to transfer string with new characters in textbox to cell), the font in the entire cell is affected. What happens is that all the characters in the cell take the font of the leading character. Is there a way to retain the font of each character?

Please Login or Register  to view this content.

**LJMetzger** · 12-20-2017, 09:52 AM

Thank you very much for the rep points.

the change in font made changes to all selected cells. This was supposed to be only for selected characters within the textbox.

The UserForm model does not allow mixing of Fonts/Colors/Style within a TextBox (or any other UserForm Control). There is allegedly a lnkEdit Control that may do what you want: https://stackoverflow.com/questions/...r-in-vba-excel

However, you are on your own if you want to pursue that path.

**Un-Do Re-Do** · 12-20-2017, 11:28 PM

I was hoping there was a way to look at each character and store the properties in an array.
Thanks for your assistance.

**Andy Pope** · 12-21-2017, 05:06 AM

You can determine the font attributes of each character in a cell

For example with the following text in a cell, Arial Calibri Times New Roman, with each font name formatted in corresponding font.
You can use the follow line in immediate window

Please Login or Register  to view this content.

to generate this information.

Please Login or Register  to view this content.

So you can store the information and then reapply to new text in the cell but it will be based on character position only

The same information can not be displayed in the textbox on a userform as the control does not allow for mixed formatting.