Adding Text and Color to a Note Box

EE Pros,

I have this really cool macro that EE Pros helped me with.  I have two simple adds I'd like to put into the Macro.

1.) When you show the Text Box, I'd like it to have the word "NOTES:" show up first on the primary line.  
2.) I'd like the Text Box for NOTES to have a YELLOW Background.

Thank you,

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
I wrote sub NotesFormatter to apply the yellow background color, and add TEXT: to your textbox. For grins, I made TEXT: 18 point and blue, while the balance of text is 11 point and black. I also changed your existing sub ShowTextBox to call NotesFormatter and to set the width of the textbox to 150 rather than your value of 10.
Sub ShowTextBox()
Dim shp As Shape
Dim ws As Worksheet
Dim wn As Window
Dim sngHeight As Single, sngLeft As Single, sngTop As Single, sngWidth As Single

Set ws = ActiveSheet
On Error Resume Next
Set shp = ws.Shapes("Notes")
On Error GoTo 0
Set wn = Application.ActiveWindow
sngLeft = wn.Left + ws.Cells(wn.ScrollRow, wn.ScrollColumn).Left + wn.Width * 0.5
sngWidth = 150
sngTop = wn.Top + ws.Cells(wn.ScrollRow, wn.ScrollColumn).Top + wn.Height / 8
sngHeight = 800

'ws.Protect Password:="jam", userinterfaceonly:=True, DrawingObjects:=False
'ActiveSheet.Unprotect Password:="jam"

If shp Is Nothing Then
    Set shp = ws.Shapes.AddTextbox( _
        Orientation:=msoTextOrientationHorizontal, Left:=sngLeft, Top:=sngTop, Width:=sngWidth, Height:=sngHeight)
    shp.Name = "Notes"
    shp.Top = sngTop
    shp.Left = sngLeft
    shp.Visible = msoTrue
End If

shp.TextFrame2.AutoSize = msoAutoSizeShapeToFitText

NotesFormatter shp
'Selection.PrintObject = 0

'Selection.PrintObject = msoFalse

End Sub

Sub NotesFormatter(shp As Shape)
Dim s As String

With shp.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 255, 0)
    .Transparency = 0
End With

With shp.TextFrame
    s = .Characters.Text
    If Len(s) > 5 Then
        If UCase(Left(s, 5)) <> "TEXT:" Then s = "TEXT:" & vbLf & s
        s = "TEXT:" & vbLf & " "
    End If
    .Characters.Text = s
    .Characters(1, 5).Font.Size = 18
    .Characters(1, 5).Font.ColorIndex = 23
    .Characters(6, Len(s) - 5).Font.Size = 11
    .Characters(6, Len(s) - 5).Font.ColorIndex = 1
End With
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:

Great!  Fastest solution I've ever had returned!  Integrated it tonight.  I am following up with you tomorrow.

Hope you had a great Easter!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.