Excel VBA Introduction Part 52.1 - Introduction to Shapes

[@paulaparo1583]

Another outstanding tutorial! Thank you so much, Sir. Four years after publication these videos are still adding value to Excel vba programmers , like me.

[@WiseOwlTutorials]

Very happy to hear that Paul! Thank you for watching and taking the time to leave a comment, it's much appreciated!

[@AamirSaeedTajalli]

Vdo is very precise, to the point and designed keeping in mind FAQs.. You answered all the question that poped out in my mind. Well done and thanks a lot.

[@WiseOwlTutorials]

Thanks Aamir, happy to hear that you found this one useful! Thanks for watching and leaving a comment!

[@AamirSaeedTajalli]

My friend.. no buttering at all... but your all videos are truly helping me and many more like me.. GREAT STUFF. Thanks for your efforts and openness to share your knowledge and experience. May the almighty ALLAH bless you and your family.

[@donbogdala5428]

Andrew, what would I do without you! Your timing couldn't have been more perfect! I am currently working on a personal project involving shapes. This has given me some ideas on where to start.

[@songokussj4cz]

You just covered what is my assignment at work. (Creating Gantt diagram with bars as shapes). And you blow my mind that range(A1:A3) has .width, .height methods. :-) I was calculating this by substracting the left cell from right cell + 1 values. Thanks for that! You're awesome.

[@grantgatland692]

Excellent - I like the explanations of why each element is added/needed in a string of commands (helps to avoid guesswork on syntax)

[@WiseOwlTutorials]

Thanks Grant, happy to hear you found it useful!

[@vadims9554]

Hello Andrew - I wanted to say thank you for your excel vba video tutorials. I have been watching it for past year or so and I found them to be clear and very informative. Many thanks!

[@saeedlawaf2809]

thank you so much for this complete course. it is really useful. 🌹💖🙏

[@WiseOwlTutorials]

You're very welcome Saeed, thanks for watching!

[@tajsay]

this is so great tutorials...i am loving it.........thank Andrew and WiseOwal team for your efforts

[@ThuhOthers]

This is exactly what I was looking for. Thank you so much!!!

[@WiseOwlTutorials]

You're welcome, glad you found it useful and thanks for watching!

[@davidjones5319]

Excellent. Thank you

[@WiseOwlTutorials]

You're welcome David, thanks for watching!

[@falstaff0808]

I've been struggling with control over some section header graphics in an automated Word document, using the .Shapes controls. Once I saw this video title in the playlist, I skipped past all those Chart (booooring!) ones to get straight to these sweet nuggets of instruction. I am looking forward to improving my codez with your sublime wisdom!

[@numchuck180]

Fantastic! this is really helpful series of videos! something i'm having trouble finding information for is dealing with shapes in groups, specifically how to reference child shapes and parent shapes

[@WiseOwlTutorials]

Hi! You might find this useful docs.microsoft.com/en-us/office/vba/api/excel.groupshapes I hope it helps!

[@krn14242]

Very useful video Andrew. Thank-you Sir.

[@ardeshiraqayi9976]

Thanks a lot for the video. Could you please teach me how can we control the space among the shapes and put gap between them, both horizontally and vertically ? Thanks again

[@WiseOwlTutorials]

Hi Ardeshir! I think you will find this video helpful kzbin.info/www/bejne/iJ6QXouQZb-phKc It shows how to position pictures on a worksheet in rows and columns. I hope it helps!

[@MegaWatchutube]

This is so great, thanks.

[@WiseOwlTutorials]

You're welcome Orlando, thank you for watching!

[@vishnupriyan5769]

Hi sir... Ur videos are awsome thanks... How to read the text from shapes ..? Can u pls explain...

[@bharatisgreate]

fantastic tutorial...thank you so much!!

[@ashutoshw220]

Great teaching!!!

[@jeethendraprabhu8707]

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.

[@sasavienne]

Thank you so much for sharing this video. 💯 Likes

[@augustodemelo915]

Great tutorial!!!!

[@mridulbhattarai5808]

I want to change the size of a shape according to the value entered in the cell. How can I do that?

[@WiseOwlTutorials]

The basic version looks like this: Dim sh As Shape Set sh = Sheet1.Shapes(1) sh.Width = Range("A1").Value

[@gauravjain5159]

Good Morning Andrew!! Hope you are well. I was looking for any ActiveX Control tool to display graph on the Form. Is there any such tool available or do we hv to use image tool to generate graph in Excel sheet and import over Form ? If you have any video for similar topic pls redirect thanks a million again!!

[@WiseOwlTutorials]

Hi Gaurav! I'm well thank you, I hope you are too! There used to be a chart tool which you could add to a VBA user form but I think that this was part of the common controls library which is no longer distributed with Excel. It looks like the best option is described here stackoverflow.com/questions/31598704/displaying-live-chart-data-in-excel-userform I'm sorry but we don't have a video on that topic but I'll add this to the to do list!

[@gauravjain5159]

Thanks a million Andrew once again :) Your responses are always helpful.

[@WiseOwlTutorials]

@@gauravjain5159 You're very welcome Gaurav!

[@satishkumarkrishnamurthy2581]

how to know the index no. of a particular shape?

[@WiseOwlTutorials]

I don't think that there's a property to return this (shapes do have an ID property but this doesn't correspond to the index number). The easiest thing to do is loop through the shapes collection to identify each shape by index and name like so Dim i As Integer For i = 1 To ActiveSheet.Shapes.Count Debug.Print i, ActiveSheet.Shapes(i).Name Next i

[@LarsIngeHolen]

Do you have an tutorial where you can do all this (insert shape, resize shape, recolor shape and add text) using for form controls? It WOULD be VERY VERY helpful!!!

[@WiseOwlTutorials]

Hi Lars, We don’t have a video which covers how to control shapes using form controls but this older video does explain how to assign subroutines to form control buttons www.wiseowl.co.uk/vba-macros/videos/vba-basics/buttons-toolbars-shortcuts/ I hope that helps!

[@mihaibr206da]

HI.can i add a shape in userform1/frame1/listbox1 ?

[@WiseOwlTutorials]

Hi! No, you can't draw shapes on a userform in the same way. You could use the Image control to insert a picture of a shape but it's a different technique to the one shown here.

[@pikachubr007]

thanks for your video. but i want to draw 1 straingth line in 01 range (ex: A1) How to do it. could u help me. pls. thanks

[@WiseOwlTutorials]

Hi Dan! Did you check out the part of the video which explains "Positioning Relative to a Range"? You can combine these techniques with the Shapes.AddLine method docs.microsoft.com/en-us/office/vba/api/excel.shapes.addline I've made an answer video to explain how this works kzbin.info/www/bejne/jYLcaXxsh7Cohas It's only available to channel members with the early access perk at the moment!

[@nishantkumar9570]

Thank you very much. Video is really very helpful. Is there any way to add a floating shape in excel?

[@nishantkumar9570]

Well, by floating shape i mean when i scroll the worksheet down, shapes also scroll with it so that the button assigned to shape is always available for click no matter how down in the worksheet i go. Thank you for your reply.

[@nishantkumar9570]

Thank You very much.

[@mohamedabdelrahman169]

Thanks for the video , you are amazing.. I am working on excel sheet which contains images and pictures and I want to l to know the location "Rows, Columns" for each shape... can you help

[@kakimotoK20]

Hi Andrew, I haven't watched this yet, but have a problem regarding shapes. I have about 20 toggle buttons that I want to replace with 3D shapes. The toggles are tied together, so that only one can be selected at a time and I'm not sure how to do this with a shape because they don't have a True False status. My toggle code is: ... .Range(.Columns("h"), .Columns("r")).Hidden = ToggleButton1.Value End With Next If Me.ToggleButton1.Value = True Then Exit Sub Me.ToggleButton2.Value = True Me.ToggleButton3.Value = True ... End with End Sub I'm a total noob in VBA. Can you help, or tell me which video of yours covers how I can get shapes to act as toggles while only have one selected at a time?

[@iceiceisaac]

how can i add shapes into the cell itself tho?

[@WiseOwlTutorials]

Hi! You can't insert drawing objects into a cell (although you can align objects to cells using the Left and Top properties). Do you mean emojis or wingding style shapes? If so, there's some useful code shown on this post stackoverflow.com/questions/55195005/can-anyone-explain-how-to-insert-emojis-to-vba-excel I hope it helps!

[@MarekLS60]

Great video

[@justusvermehren9198]

Hey Andrew, nice video. But how can you change the texts in the different fields of the smart art per VBA? :o

[@jayjayf9699]

I have shapes in my active sheet , I do debug.print shapes(1).name and it doesn’t work , how come when I do debug.print worksheets(1).name that works but it doesn’t work for the shapes collection? Also I’ve checked the object browser for these collection and both don’t have a name property in the member of the object .

[@WiseOwlTutorials]

Hi there, The Shapes property doesn't have an implicit scope so you need to qualify the reference to it by referring to an object which has a Shapes property (such as a worksheet or a chart), e.g. Worksheets("Sheet1").Shapes(1).Name The Worksheets property has an implicit scope and so you don't need to qualify it, although you can if you like, e.g. Workbooks("Book1.xlsx").Worksheets("Sheet1") The Shapes property returns a reference to an object of the Shape class and, similiarly, the Worksheets property returns a reference to an object of the Worksheet class. In the Object Browser, look for the Shape and Worksheet classes rather than the Shapes and Worksheets classes to see the correct list of methods and properties. I hope that helps!

[@jayjayf9699]

@@WiseOwlTutorials thanks, your tutorial saved my life learning vba, especially as the management didn't offer me training

[@WiseOwlTutorials]

Cheers Jay, happy to hear that it helped!

[@carlosrodriguez0112]

thank, that is so great tutorial

[@jayjayf9699]

Hi I am tearing my hair out over the hangman loop vba problem on your website can you give me advice for please ?

[@WiseOwlTutorials]

:D if it makes you feel better that's the most difficult exercise we give out on our Introduction to Excel VBA course! How far did you get? If you can post some of your code here I might be able to point you in the right direction without giving the full answer!

[@jayjayf9699]

@@WiseOwlTutorials I think I've done most of it, have u got an email, so I can send you code?

[@WiseOwlTutorials]

Jay I'd love to be able to respond personally by email to every request for help but sadly if I did that I wouldn't have time for anything else. You're welcome to post some code here and I can try to give you a few hints.

[@jayjayf9699]

@@WiseOwlTutorials Option Explicit Sub hangman() Dim sh As Shape Dim guess As String Dim hello As Boolean Dim wordcell As Range Dim guessletter As String Dim correctguess As Boolean Dim blackfont As Boolean Dim shapecount As Double ' restart game clear all the shapes and values For Each sh In Worksheets("Game").shapes sh.Visible = msoFalse Next sh ' Worksheets("Game").Range(Cells(1, 2), Cells(1, 20)).Delete ' Worksheets("Game").Range(Cells(1, 2), Cells(1, 20)).Font.Color = vbWhite Application.ScreenUpdating = True shapecount = 0 If Range("B1").Value = "" Then MsgBox "Please enter the word to be guessed and restart macro." Exit Sub End If Do While notvisiable = True correctguess = False guessletter = InputBox("Please enter your guess letter") For Each wordcell In Range(Cells(1, 2), Cells(1, Range("A1").End(xlToRight).Column)) If wordcell.Value = guessletter Then wordcell.Font.Color = vbBlack correctguess = True End If Next wordcell 'blackfont = False 'For Each wordcell In Range(Cells(1, 2), Cells(2, Range("B1").End(xlToRight).Column)) 'If wordcell.Font.Color = vbBlack Then ' blackfont = True 'End If If correctguess = False Then shapecount = shapecount + 1 Worksheets("Game").shapes(shapecount).Visible = msoTrue End If 'Next wordcell If guessed = True Then MsgBox " you won!!!" Exit Sub End If Loop 'For Each sh In Worksheets("Game").shapes ' sh.Visible = msoTrue 'Next sh End Sub Function notvisiable() As Boolean notvisiable = False Dim sh As Shape For Each sh In Worksheets("Game").shapes If sh.Visible = msoFalse Then notvisiable = True End If Next sh End Function Function guessed() As Boolean Dim wordhasbeenguessed As Boolean Dim lengthofword As Double Dim wordnumb As Range Dim count As Integer count = 0 wordhasbeenguessed = True For Each wordnumb In Range(Cells(1, 2), Cells(1, Range("A1").End(xlToRight).Column)) If wordnumb.Font.Color = vbWhite Then wordhasbeenguessed = False End If count = count + 1 Next wordnumb guessed = wordhasbeenguessed lengthofword = count End Function

[@jayjayf9699]

@@WiseOwlTutorials what do you think of my hangman solution?

[@forzaalgiers2045]

Great video , thank you !!

[@باليتركي]

thanks

[@lovestreaming]

Great help, as usual. Thanks a lot! :)

[@redhaakhund1271]

🙏👍👍👍👍👍

[@WiseOwlTutorials]

Thanks!