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 23, 2020

Riny_van_Eekelen

Hi Riny_van_Eekelen, I was wondering if you could give me a little more advise please. (sorry to be a bore!!)

This time I am trying to produce an arrow and place it anywhere on the spreadsheet.

As per my problem with the text box, I can produce the arrow but not place it anywhere!! Yesterday I tried all combinations, without success, I am obviously not using the correct script. Could you kindly suggest what I should be doing.

My basic macro is as follows:-

Sub Blue_Arrow()
'
' Blue_Arrow Macro
'
ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 200, 50, 300, 50).Select
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
End Sub

I look forward to your response. Many thanks. Roger.

Riny_van_Eekelen
Platinum Contributor
Mar 23, 2020
Abedules
It's quite similar to the other code.
Sub Blue_arrow() X = ActiveCell.Left Y = ActiveCell.Top ActiveSheet.Shapes.AddConnector(msoConnectorStraight, X, Y, X + 50, Y + 50).Select Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle With Selection.ShapeRange.Line .Visible = msoTrue .Weight = 4.25 End With End Sub
Play around with the X and Y. The above code takes the current cell as the start for the arrow and than it will point 50 pixels down and 50 pixels to the right.
Use X - 50, Y - 50, X, Y and the arrow will start up and left 50 pixels and point exactly to the the active cell.
- Abedules
  Copper Contributor
  Mar 23, 2020
  Riny_van_Eekelen
  Many thanks, when I see it, its all so understandable.
  Am I correct in thinking that when one uses a comment such as "ActiveCell." ,one can only use the suggested parameters as in the drop down menu at that point?
  - Riny_van_Eekelen
    Platinum Contributor
    Mar 23, 2020
    Abedules
    In this particular code, the "Activecell" just tells it to put the arrow in the cell that is selected at the time you execute the macro. Don't understand your question about "parameters as in the drop down menu at that point"