Forum Discussion
Creating Macro for Text Box
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
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 SubHi 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 SubI look forward to your response. Many thanks. Roger.
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 SubPlay 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.
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?
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.
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.
No probs, now working on some other ideas based on your guidance.
Many thanks
