textbox font style

[Bert]

Is there a way to use font styles (bold, italic, underline) in a textbox,
that is, with some text, I'd like to add a style to a portion of the text,
e.g., a book title, but leave other text "normal". Is this possible, and if
so, if I copy the contents of a textbox to a cell, does the style
information transfer as well?
Thanks

 

[JLGWhiz]

Here is a sample for a textbox on a worksheet. It should also work on a
userform textbox.

Sub jdk()
With Sheets(1).TextBox1.Font 'Or UserForm#
.Italic = True
.Bold = True
End With
End Sub

I have not tried using Left, Right or Characters functions to mix the fonts.
You can do that.

 

[JLGWhiz]

P.S.
When assigning a control value to a cell, VBA utilizes the font of the
receiving cell. Also, when pasting to a cell in normal mode, VBA utilizes
the font of the source cell. See PasteSpecial in the VBA help file for more
info on pasting formats.

 

[Rick Rothstein \(MVP - VB\)]

The ActiveX TextBox does not support differing font treatments for parts of
its text, so UserForm TextBoxes are out as are those directly on the
worksheet if from the Visual Basic toolbar. However, you can use a TextBox
Shape object from the Drawing toolbar (directly on your worksheet) which
does support the individual font treatments you are seeking. If you place
the TextBox on the worksheet manually (and assuming its name is the default
"Text Box 1"), then this code can be used (as a guideline) to fill it with
text and change the font treatment of a part of it...

Sub MakeTextBold()
With Worksheets("Sheet1").Shapes("Text Box 1").TextFrame
.Characters.Text = "Read 'Your Book Title' today!"
With .Characters(7, 15).Font
.Bold = True
.Italic = True
.Underline = True
End With
End With
End Sub

If you need to do this only one time, you can create the TextBox directly
from code and then fill it as you wish. Here is some sample code you can use
as a guideline for that...

Sub MakeTextBold()
With Worksheets("Sheet1")
.Shapes.AddTextbox msoTextOrientationHorizontal, 100, 200, 200, 20
With .Shapes(.Shapes.Count).TextFrame
.Characters.Text = "Read 'Your Book Title' today!"
With .Characters(7, 15).Font
.Bold = True
.Italic = True
.Underline = True
End With
End With
End With
End Sub

Rick

 

[Bert]

Rick:
Thanks for that information. I've been poking around and came across a
control called richtextbox, but I don't think it's available to VBA. The
problem with your solution is, I have to use it in an existing Userform.
But what you've described is exactly what I'd like to be able to do.
Thanks.

 

[Rick Rothstein \(MVP - VB\)]

I have a Rich TextBox available to me, but I am not sure if it came with my
compiled version of VB (I make use of it on that version of VB all the time)
or it is a natively available Windows control. If you haven't already done
so, right-click the UserForm's Toolbox and select "Additional Controls" from
the popup menu that appears. Scroll the list looking for this exactly worded
item...

Microsoft Rich Textbox Control 6.0 (SP6)

although I'm not so sure that the 6.0 (SP6) won't be different on different
systems. If you see it in the list, mark its check box and you will be able
to use it. I guess the only danger if you are giving your UserForm code to
others is they may not have this control available to them (that would be
the case if the control actually comes with a program that installs the
compiled version of VB6). The reason I'm afraid it comes with the compiled
version of VB is that SP6... my version of compiled VB is at Service Pack 6
and my version of Vista is not.

Rick

 

[Bert]

I've done a little checking. I have the control, but when I try to place it
on a UserForm, I get an error message (The subject is not trusted for the
specified action".) Seems that beginning with Office 2003, there were
security rules put in place that prevents its use. I found a MSDN message
that shows a fix involving a registry change:
Registry Key :
HKEY_LOCAL_MACHINE\Software\Microsoft\Internet Explorer\ActiveX
Compatibility\{3B7C8860-D78F-101B-B9B5-04021C009402}


Change the "Compatibility Flags" DWORD value to 0 (0x400 is kill bit). Now
you'll get prompted to trust loading the control and it will run.

But...I'm not sure I want to mess with the registry settings on the other
machines this workbook (and UserForm) will be used on.
Thanks for your help, though. If I decide to proceed, I have the control I
need!

Bert

 

[Neil]

Bert:

You can use a third-party rich textbox that uses the same underlying
technology that the Microsoft rich textbox does. FMS sells one that works
with Access; not sure if it works with Excel. Also Stephen Lebans has one on
his website -- again, designed for Access, but might work with Excel. And
there are other third party add-ons.

What others have done is created a VB wrapper for the control and created
their own OCX.