Forum Discussion
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
Every time I run this macro in any worksheet, the brown text box appears in the location of cell D5.
I want it to appear in a different area (other than D5) of the worksheet that I select but it always defaults to D5
How can I overcome this?
I have watched numerous videos but none actually relate to what I want to do.
I don’t want to mislead you by the mention of cell D5. I only mentioned this because it was in that area of the worksheet that I created the text box, not actually in cell D5 and it is in this area where the box always returns when I run the macro.
My desire is to produce the box in any area of the worksheet.
This is my coding which I hope will explain things:
Sub Create_Brown_Text_Box()
' Create_Brown_Text_Box Macro
'
Range("D5").Select
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 153.6, 33, 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
Range("F9").Select
End Sub
I hope this helps. Thank you.
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
11 Replies
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 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.
