Forum Discussion

Copper Contributor

Mar 22, 2020

Solved

Creating Macro for Text Box

I am learning about macros in excel. So far I have opened a worksheet, clicked in a cell (D5), recorded a macro of inserting a text box and colouring it brown. The macro is saved in Personal.XLSB ...

Mar 22, 2020

Try this one

Sub Create_Brown_Text_Box()
' Create_Brown_Text_Box Macro

    X = ActiveCell.Left
    Y = ActiveCell.Top
    
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, X, Y, 109.2, _
        77.4).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.5
        .Transparency = 0
        .Solid
    End With

End Sub

Riny_van_Eekelen

Platinum Contributor

Mar 22, 2020

Abedules

Try this one

Sub Create_Brown_Text_Box()
' Create_Brown_Text_Box Macro

    X = ActiveCell.Left
    Y = ActiveCell.Top
    
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, X, Y, 109.2, _
        77.4).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.5
        .Transparency = 0
        .Solid
    End With

End Sub

Abedules

Copper Contributor

Mar 22, 2020

Riny_van_Eekelen

Many thanks for your suggestion.

The macro program suggested I remove the "underscore" in the measurements but apart from that all great.

It has given me a much better understanding.

Many thanks for your help.

Riny_van_Eekelen
Platinum Contributor
Mar 22, 2020
Abedules You're welcome!

Didn't bother to delete the underscore. It came from your own code and I just made som small modifications to make it work.
- Abedules
  Copper Contributor
  Mar 22, 2020
  No probs, now working on some other ideas based on your guidance.
  Many thanks