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.
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!
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!
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 ruclips.net/video/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 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?
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!
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!
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 ruclips.net/video/WQy8I6RKuOI/видео.html It's only available to channel members with the early access perk at the moment!
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 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!
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!
: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
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
Another outstanding tutorial! Thank you so much, Sir. Four years after publication these videos are still adding value to Excel vba programmers , like me.
Very happy to hear that Paul! Thank you for watching and taking the time to leave a comment, it's much appreciated!
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.
Thanks Aamir, happy to hear that you found this one useful! Thanks for watching and leaving a comment!
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.
Excellent - I like the explanations of why each element is added/needed in a string of commands (helps to avoid guesswork on syntax)
Thanks Grant, happy to hear you found it useful!
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!
This is exactly what I was looking for. Thank you so much!!!
You're welcome, glad you found it useful and thanks for watching!
thank you so much for this complete course. it is really useful. 🌹💖🙏
You're very welcome Saeed, thanks for watching!
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.
this is so great tutorials...i am loving it.........thank Andrew and WiseOwal team for your efforts
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!
Excellent. Thank you
You're welcome David, thanks for watching!
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
Hi! You might find this useful docs.microsoft.com/en-us/office/vba/api/excel.groupshapes
I hope it helps!
Thank you so much for sharing this video. 💯 Likes
Very useful video Andrew. Thank-you Sir.
This is so great, thanks.
You're welcome Orlando, thank you for watching!
fantastic tutorial...thank you so much!!
Great teaching!!!
Great tutorial!!!!
Great video , thank you !!
Great video
thank, that is so great tutorial
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 ruclips.net/video/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.
thanks
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?
Wow. That's great tutorial for me. But I want to ask, how to lock size and location shape after we controlling it using VBA?
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!
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!
Thanks a million Andrew once again :) Your responses are always helpful.
@@gauravjain5159 You're very welcome Gaurav!
Hey Andrew, nice video.
But how can you change the texts in the different fields of the smart art per VBA? :o
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
Great help, as usual. Thanks a lot! :)
I want to change the size of a shape according to the value entered in the cell. How can I do that?
The basic version looks like this:
Dim sh As Shape
Set sh = Sheet1.Shapes(1)
sh.Width = Range("A1").Value
🙏👍👍👍👍👍
Thanks!
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
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 ruclips.net/video/WQy8I6RKuOI/видео.html It's only available to channel members with the early access perk at the moment!
Thank you very much. Video is really very helpful.
Is there any way to add a floating shape in excel?
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.
Thank You very much.
HI.can i add a shape in userform1/frame1/listbox1 ?
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 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!
@@WiseOwlTutorials thanks, your tutorial saved my life learning vba, especially as the management didn't offer me training
Cheers Jay, happy to hear that it helped!
how to know the index no. of a particular shape?
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
how can i add shapes into the cell itself tho?
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!
Hi I am tearing my hair out over the hangman loop vba problem on your website can you give me advice for please ?
: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!
@@WiseOwlTutorials I think I've done most of it, have u got an email, so I can send you code?
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
@@WiseOwlTutorials what do you think of my hangman solution?