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

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"
  - Abedules
    Copper Contributor
    Mar 23, 2020
    Riny_van_Eekelen
    " Don't understand your question about "parameters as in the drop down menu at that point" >
    As I type the following : "X = ActiveCell." I get a drop down menu of suggestions to use after I put in the full stop.
    I am assuming that one can only use these drop down menu suggestions and not make up my own?
    
    Once again, thanks for your support.