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
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
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
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?
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.
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
@@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!
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.
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.
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.
![Computer Application for Biotechnology - Module 3[MS-Excel], MCQ Practice - 1](http://i.ytimg.com/vi/I2qDbn04QfU/mqdefault.jpg)
Great video on shapes.
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
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
Dear sir, add shape is good vba code , can you advance ( how to link shape with combobox
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
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?
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.
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
@@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!
@@ExcelMacroMania didn't know that was possible. Thank you
Hi, how create custom events to shapes?
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.
@@ExcelMacroMania fine, i try this
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.
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.
Not show shapes option in vba form in toolbox
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.