Excel VBA Objects: Shape Object (Part 1)

Подписаться 7 тыс.

Просмотров 14 тыс.

50% 1

Get the Excel VBA Objects Guide for Intermediate with extended and revised content here: excelmacroclas...
Welcome to the Excel VBA Objects Series. In this video we see how to work with the Shape object. We learn to target and reference the Shape object in a worksheet, what are the different types of shapes and auto shapes in Excel and Microsoft Office, how to add a shape, and how to format the shape. In the next video we'll see how to add and format other shapes, and how to target specific types of shapes and delete a particular shape or all shapes in a worksheet.
MsoAutoShapeType Enumeration here:
excelmacroclas...
Find more content and numerous macro examples and other Excel VBA learning materials in the Excel Macro Class blog under the direct link: excelmacroclas...
And yet, if you want more, you can find various Excel templates, dashboards, and applications of different nature in the other blogs of the Excel Macro Mania saga:
Excel Macro Fun (excelmacrofun....)
Excel Macro Business (excelmacrobusi...)
Excel Macro Sports (excelmacrospor...)

Опубликовано:

19 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 17

@krn14242 Год назад

Great video on shapes.

@jeethendraprabhu8707 6 месяцев назад

Hi I have data in which I have column A with small shapes (Red, Green & Yellow). And I have to update Column B with color names (Green, Red & yellow). how can I do it? I can select all the shapes through VBA, but I cannot loop through the shapes. please help.. thanks in advance.

@ExcelMacroMania 6 месяцев назад

First you need to know the name of the shape in that particular row. Then you can get the color of the shape with the following: ActiveSheet.Shapes("shpName").Fill.BackColor.RGB That returns a number that represents the RGB color, so you need to compare it with the numbers for blue, red, amber.

@janiko4271 Год назад

hi super helpful videos, i am trying to report for football. looking at target areas in the goal, selection one is all goals for each traget area, selection two is only shots that were succesful/scored and where they were targeted. i am trying to create textbox's for to paste over each zone when wanting to look at selection two, and then delete again when selection one is wanted. i am struggling to have the textbox create in line with the merged cell, e.g. a1 to b1 are merged so the text box is created in the middle rather than the left corner being the top left corner of a1

@ExcelMacroMania Год назад

You need to do some math to get the position you want. For example, the code below adds the text box in the center of range A1:B1, from the center of cell A1 to the center of cell B1 horizontally, while it fits the whole vertical height. You can change the txbTop and txbHeight to center vertically too. You can play with the numbers depending where exactly you wanna put it. Dim rng As Range Set rng = Range("A1:B1") rngWidth = rng.Width rngHeight = rng.Height txbLeft = rng.Left + (rngWidth / 4) txbTop = rng.Top txbWidth = rngWidth / 2 txbHeight = rngHeight Sheet1.Shapes.AddTextbox msoTextOrientationHorizontal, _ txbLeft, txbTop, txbWidth, txbHeight

@indianmonster1445 Год назад

Dear sir, add shape is good vba code , can you advance ( how to link shape with combobox

@ExcelMacroMania Год назад

You can add a combobox with Shapes.AddFormControl and choosing the type xlDropDown: Sheet1.Shapes.AddFormControl xlDropDown, 50, 50, 50, 50 If you want to link or group that with other shape (for example, a rectangle), you have to target the range array as follows: Sheet1.Shapes.Range(Array("Rectangle 1", "Drop Down 2")).Group

@josephdaquila2479 11 месяцев назад

Does anyone know how to work with shapes that are already on the page? Buttons to be more specific. It seems like I cant get VBA to edit the properties of the button. I'm wondering if I need to go into design mode first somehow?

@ExcelMacroMania 11 месяцев назад

That's precisely covered at the beginning of this video. ActiveSheet.Shapes("Button 1").Select (or any other property/method) But that's a normal form control button (design mode does not apply here). If you have an ActiveX control command button you can refer to it as above or using directly the name: ActiveSheet.CommandButton1....However, for an ActiveX control running the macro will exit design mode and some of the properties or methods may not apply. So, probably, you need to use a normal control for what you want to do if it does not let you do it with ActiveX. ActiveX controls are usually preferred when you need to apply events as they come app the sheet module window for the sheet they belong and accept a number of even procedures there.

@josephdaquila2479 11 месяцев назад

Thank you for your reply. Sorry I missed that explanation/ didn't fully absorb it. I seemed to be able to use the .Select method on an ActiveX button with success. From there I can get edit the properties of the selection just fine. I am mainly working on creating a way to swap between different states of a singular button. My idea is to send the one button to the back, make it white (to blend in with the sheet), and disable the button that gets sent to the back. And run that reverse process for the button I really want at that moment (i.e. bring it to the front, color it, and enable it) Thanks again@@ExcelMacroMania

@ExcelMacroMania 11 месяцев назад

@@josephdaquila2479 You should probably use the "Visible" property instead: ActiveSheet.Shapes("Button 1").Visible = False ... then True to show. Same for ActiveX, here you can use Shapes or not. ActiveSheet.CommandButton1.Visible = False Good luck!

@josephdaquila2479 11 месяцев назад

@@ExcelMacroMania didn't know that was possible. Thank you

@Godfathergameing01 Год назад

Not show shapes option in vba form in toolbox

@ExcelMacroMania Год назад

Not sure what you mean. It sounds like you are trying to add shapes in a userform? There is no shape option indeed, you can just add a picture.

@canaldoferrarezzi4687 Год назад

Hi, how create custom events to shapes?

@ExcelMacroMania Год назад

Do you refer to an event such as clicking the shape? In that case you just add: shp.OnAction = "MyOtherMacro" where shp is a Shape object variable where a given shape or newly added shape has been assigned to (as explained in the video), and MyOtherMacro is the target macro you want to run when clicking the shape.

@canaldoferrarezzi4687 Год назад

@@ExcelMacroMania fine, i try this