Excel: insert rectangle

Get help with using AutoHotkey and its commands and hotkeys
Luis Diaz
Posts: 26
Joined: 04 Jun 2020, 16:56

Excel: insert rectangle

25 Jul 2020, 16:43

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.
Last edited by BoBo on 25 Jul 2020, 17:18, edited 1 time in total.
Reason: Moved to 'Ask for Help'.
User avatar
flyingDman
Posts: 883
Joined: 29 Sep 2013, 19:01

Re: Excel: insert rectangle

25 Jul 2020, 17:55

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
User avatar
Datapoint
Posts: 76
Joined: 18 Mar 2018, 17:06

Re: Excel: insert rectangle

26 Jul 2020, 07:56

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 := ""
User avatar
flyingDman
Posts: 883
Joined: 29 Sep 2013, 19:01

Re: Excel: insert rectangle

26 Jul 2020, 12:31

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
Posts: 26
Joined: 04 Jun 2020, 16:56

Re: Excel: insert rectangle

26 Jul 2020, 12:44

Thank you very much, it works!
Could you please help me to align the format with following rectangle shape attached?
Attachments
ExcelShape.xlsx
(9.38 KiB) Downloaded 10 times
Luis Diaz
Posts: 26
Joined: 04 Jun 2020, 16:56

Re: Excel: insert rectangle

26 Jul 2020, 13:49

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

Return to “Ask For Help”

Who is online

Users browsing this forum: djuga, PuzzledGreatly and 35 guests