Another outstanding tutorial! Thank you so much, Sir. Four years after publication these videos are still adding value to Excel vba programmers , like me.
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.
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.
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.
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.
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!
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!
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
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
Hi Ardeshir! I think you will find this video helpful ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Rm--XZ0ZvNE.html It shows how to position pictures on a worksheet in rows and columns. I hope it helps!
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.
Hi there! The easiest way to do this is to protect the worksheet that the shape is on. You can do this manually or using VBA: Worksheets("Sheet1").Protect I hope it helps!
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!!!
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!
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
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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-WQy8I6RKuOI.html It's only available to channel members with the early access perk at the moment!
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?
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!!
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!
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.
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.
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
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!
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 .
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!
: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!
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.
@@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
