Excel: insert rectangle

[Luis Diaz]

Hello,

I have the following procedure which allows me to insert rectangle in excel.

Could you please help me to transpose it in AutoHotkey?

Code: Select all

Sub AddRectangle()
    With ActiveSheet.Shapes.AddShape(msoShapeRectangle, 10, 10, 200, 100).TextFrame
        .Characters.Text = "This is a rectangle"
        .HorizontalAlignment = xlHAlignCenter
        .VerticalAlignment = xlVAlignCenter
    End With
End Sub

Thank you for your help.

[flyingDman]

Code: Select all

xl := ComObjActive("Excel.application")
rect := xl.ActiveSheet.Shapes.AddShape(1,10, 10, 200, 100).textframe.Characters.Text := "This is a rectangle"
xl.ActiveSheet.Shapes(1).TextFrame.HorizontalAlignment := -4108
xl.ActiveSheet.Shapes(1).TextFrame.VerticalAlignment := -4108
;additional settings
xl.ActiveSheet.Shapes(1).Fill.ForeColor.RGB := 13494510                    ; http://cloford.com/resources/colours/500col.htm
xl.ActiveSheet.Shapes(1).Line.ForeColor.RGB := 11651277
xl.ActiveSheet.Shapes(1).TextFrame.Characters.Font.Color := 35723
xl.ActiveSheet.Shapes(1).TextFrame.Characters.Font.Size := 36

[Datapoint]

Code: Select all

msoShapeRectangle := 1, xlHAlignCenter := -4108, xlVAlignCenter := -4108
xl := ComObjActive("Excel.application")
TxtFrm := xl.ActiveSheet.Shapes.AddShape(msoShapeRectangle, 10, 10, 200, 100).TextFrame
TxtFrm.Characters.Text := "This is a rectangle"
TxtFrm.HorizontalAlignment := xlHAlignCenter
TxtFrm.VerticalAlignment := xlVAlignCenter
xl := TxtFrm := ""

[flyingDman]

When there is only one shape to create, the presentation does not matter much in my view and you can make it as pretty as you want. It is when you have multiple shapes and and you need to keep consistency but also allow for variation that you want to put function over form. In that case you might want to use something like this:

Code: Select all

xl := ComObjActive("Excel.application")
xl.ActiveSheet.Shapes.AddShape(1,10, 10, 200, 100)
xl.ActiveSheet.Shapes.AddShape(1,10, 160, 200, 100)
xl.ActiveSheet.Shapes.AddShape(1,10, 310, 200, 100)

for shape in xl.ActiveSheet.Shapes
	{
	shape.Textframe.Characters.Text := "This is a rectangle number " a_index
	shape.TextFrame.HorizontalAlignment := -4108			; -4108 Center; -4131 Left; -4152 Right; -4130 Justify
	shape.TextFrame.VerticalAlignment := -4108
	shape.Fill.ForeColor.RGB := 13494510                    ; http://cloford.com/resources/colours/500col.htm
	shape.Line.ForeColor.RGB := 11651277
	shape.TextFrame.Characters.Font.Color := 35723
	shape.TextFrame.Characters.Font.Size := 24
	}

When it comes to assigning variables to the various Excel parameters (i.e. msoShapeRectangle := 1, xlHAlignCenter := -4108, xlVAlignCenter := -4108), I do not do it, and don't see the use of it. If I need to remind myself of the different values that I might want to use, I list them in a comment like I did above with the HorizontalAlignment. That's much more useful IMHO.

[Luis Diaz]

Thank you very much, it works!
Could you please help me to align the format with following rectangle shape attached?

[Luis Diaz]

Hi flyingDman,
Thank you very much for your proposal, it works. I am going to play with format by taking recommendations at:
http://cloford.com/resources/colours/500col.htm