I have started a brand new Excel For Freelancers TH-cam channel with new and UNIQUE training videos each week. I would love your feedback on it: th-cam.com/users/ExcelForFreelancers
Thanks so much. This was my first every try at a training video, just for fun over 6 years ago. It's been a long time since i have watched this one. Thanks for your comment.
You can also get this effect without any additional shapes by just adding a Comment (older versions) or Note (newer versions) to the cell behind button/shape. It doesn't look as good and is less flexible, but it's even easier than this. Also, it doesn't require VBA. All that said, this is still a GREAT workaround to the half-baked Excel object model that has remained unfinished for decades. It appears Microsoft no longer has the talent (or desire) to finish it, instead trying to foist new products (Power Apps, etc) on people that would be unnecessary VBA were ever finished.
vijay kumar thanks so much (I used a terrible microphone many years ago when creating this) Have a look at my new channel "Excel For Freelancers". th-cam.com/channels/XhiOv9VT_0XSnVXyEh4pWw.htmlvideos
Excellent explanation. It works well if the bigger shape is significantly larger than the smaller shape. But it does not work so well if the shapes are minute such as when you have a small help icon. The way it is described in the video requires the user to move slowly outward to give it enough time over the area of the larger shape to be detected and consequently hide the help shape again. Otherwise, as it is doing with me, the help shape remains visible. Any thoughts or alternatives?
Thanks so much Sam, that is correct it should be quite a bit larger. I have an updated video on this on my other channel here: th-cam.com/video/Oe-J3Y_Lbow/w-d-xo.html
Thanks Randy for this. Macro related question, I am trying to build a macro that connects any two shapes with a line, but not 'fix' it to specific shapes.......... To give a bit of context:- The excel sheet is being used in a presentation, which will have a number of boxes. - I want to be able to use a macro that inserts a line between any two boxes that are selected. - If I select 2 different boxes, the same macro will still insert a line between. I was wondering if you think that is possible? Many thanks
+sanaaaa007 Hi there, thats a good question, i have not tried that however via VBA you can assign a connector to shapes via the .'AddConnector' operative. I would first single out, then label the selected shapes, then once they are labeled (Shape1, Shape2) you can then assign a connector to them. Perhaps something like this: ActiveSheet.Shapes.AddConnector(msoConnectorElbow, 436.2, 105.6, 513, 106.6). _ Select Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes( _ "Rounded Rectangle 1"), 4 Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes( _ "Rounded Rectangle 2"), 2 I hope that helps a bit, it might be a start what i have provided.
Hey Randy, I really like the video. However I'm having issues with moving my shape in front of the active x labels. The labels themselves will move forward or backward relative to each other, as will shapes if I have more than one, but the shape itself will not move in front of the labels. If I just make the labels transparent, it won't matter but I was curious if you had any thoughts on how to fix this. Thanks for the video!
ClotBlock Hello There, I am glad you like the video. I do understand your point on placing shapes on top of the labels, however if I can understand what your ultimate goal is then i can perhaps point you in the right direction or make a sections if i can. With the labels transparent you should be able to create any type of look with the shapes themselves as in my sample the labels were just used for their Mouse Move functionality. What is the goal here, or what functionality are you trying to achieve?
Randy Austin I'm just trying to recreate what you do between 4:55 and 5:45 where you place the active x labels behind the rectangular shape object. For whatever reason the active labels and shape will not move behind/in front of the other and my labels always end up in front the shape. As I mentioned, I know that if I just want the same appearance as you have at 6:45 I can just set the labels to be transparent. I just wondering why I cannot move the labels to the back, relative to the shape. Thanks
Hi ClotBlock I just realized, what i believe to be the issue. I have both Excel 2007 and 2010. I am unable to move labels in Front or Back using 2007 however when using Excel 2010 i am able to complete this. Are you using 2007 Excel?
Randy Austin , Thanks!! that must be it, I haven't had a chance to look at it on 2010 or later but I was in fact using 2007 Excel when going over this example/exercise. Thank you for your help I really appreciate it. I have already thought of a few ways to work around this, but again I appreciate that you went out of your way to figure this out!
Thanks Randy for this. However, I can't get the label 'behind' the shape. I'm using this approach to create a rollover; however, due to this, whenever I try to click on that shape (to activate the macro associated with the shape), it ends up selecting the label. Any ideas ? I'm using excel 2007
Hello Ragan, thanks for liking the video. I have also ran into this issue in, 2007 so I used Excel 2010 to complete. I think that this was an issue Microsoft fixed on versions after 2012. I hope this helps. Please let me know if you have any additional questions.
This video has been so helpful. But is it possible to create two buttons on the same sheet? I have two shapes that are hyperlinked to other sheets and I would like to use the hover over effect on them so that they light up when the cursor rolls over. I have been able to do one with the method described in the video and it works perfectly, but when I to add the code to the second one nothing happens and the first one stops working. I am not an excel expert, just a fiddler and so I don't understand the order of the coding too well. Any help will be appreciated. Thanks!
Hi thanks very much. I have a new channel and a new video which uses this technique (and much better sound quality) You can see it right here: th-cam.com/video/Oe-J3Y_Lbow/w-d-xo.html
I have started a brand new Excel For Freelancers TH-cam channel with new and UNIQUE training videos each week. I would love your feedback on it:
th-cam.com/users/ExcelForFreelancers
excellent information, your production quality has gretly improved over the last several years.
Thanks so much. This was my first every try at a training video, just for fun over 6 years ago. It's been a long time since i have watched this one. Thanks for your comment.
You can also get this effect without any additional shapes by just adding a Comment (older versions) or Note (newer versions) to the cell behind button/shape. It doesn't look as good and is less flexible, but it's even easier than this. Also, it doesn't require VBA. All that said, this is still a GREAT workaround to the half-baked Excel object model that has remained unfinished for decades. It appears Microsoft no longer has the talent (or desire) to finish it, instead trying to foist new products (Power Apps, etc) on people that would be unnecessary VBA were ever finished.
Creative Idea described beautifully
vijay kumar thanks so much (I used a terrible microphone many years ago when creating this) Have a look at my new channel "Excel For Freelancers". th-cam.com/channels/XhiOv9VT_0XSnVXyEh4pWw.htmlvideos
Great workarround for the lack of Hint for Shapes in Excel. Thanks!
Excellent explanation.
It works well if the bigger shape is significantly larger than the smaller shape. But it does not work so well if the shapes are minute such as when you have a small help icon. The way it is described in the video requires the user to move slowly outward to give it enough time over the area of the larger shape to be detected and consequently hide the help shape again. Otherwise, as it is doing with me, the help shape remains visible.
Any thoughts or alternatives?
Thanks so much Sam, that is correct it should be quite a bit larger. I have an updated video on this on my other channel here: th-cam.com/video/Oe-J3Y_Lbow/w-d-xo.html
Thanks Randy for this. Macro related question, I am trying to build a macro that connects any two shapes with a line, but not 'fix' it to specific shapes..........
To give a bit of context:- The excel sheet is being used in a presentation, which will have a number of boxes. - I want to be able to use a macro that inserts a line between any two boxes that are selected. - If I select 2 different boxes, the same macro will still insert a line between.
I was wondering if you think that is possible?
Many thanks
+sanaaaa007 Hi there, thats a good question, i have not tried that however via VBA you can assign a connector to shapes via the .'AddConnector' operative. I would first single out, then label the selected shapes, then once they are labeled (Shape1, Shape2) you can then assign a connector to them. Perhaps something like this:
ActiveSheet.Shapes.AddConnector(msoConnectorElbow, 436.2, 105.6, 513, 106.6). _
Select
Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes( _
"Rounded Rectangle 1"), 4
Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes( _
"Rounded Rectangle 2"), 2
I hope that helps a bit, it might be a start what i have provided.
Excellent video, as always.
Thanks so much. This is a very old video. I'm glad you liked it
This is awesome I will give full marks sir....
Hey Randy, I really like the video. However I'm having issues with moving my shape in front of the active x labels. The labels themselves will move forward or backward relative to each other, as will shapes if I have more than one, but the shape itself will not move in front of the labels. If I just make the labels transparent, it won't matter but I was curious if you had any thoughts on how to fix this. Thanks for the video!
ClotBlock Hello There, I am glad you like the video. I do understand your point on placing shapes on top of the labels, however if I can understand what your ultimate goal is then i can perhaps point you in the right direction or make a sections if i can. With the labels transparent you should be able to create any type of look with the shapes themselves as in my sample the labels were just used for their Mouse Move functionality. What is the goal here, or what functionality are you trying to achieve?
Randy Austin I'm just trying to recreate what you do between 4:55 and 5:45 where you place the active x labels behind the rectangular shape object. For whatever reason the active labels and shape will not move behind/in front of the other and my labels always end up in front the shape. As I mentioned, I know that if I just want the same appearance as you have at 6:45 I can just set the labels to be transparent. I just wondering why I cannot move the labels to the back, relative to the shape. Thanks
Hi ClotBlock I just realized, what i believe to be the issue. I have both Excel 2007 and 2010. I am unable to move labels in Front or Back using 2007 however when using Excel 2010 i am able to complete this. Are you using 2007 Excel?
Randy Austin , Thanks!! that must be it, I haven't had a chance to look at it on 2010 or later but I was in fact using 2007 Excel when going over this example/exercise. Thank you for your help I really appreciate it. I have already thought of a few ways to work around this, but again I appreciate that you went out of your way to figure this out!
en ves de formulario, le coloco esos rectangulos de colores para luego mandarlos a traz y desaparecerlos, buena idea.
Is there a way to put like an IF condition before thise to like switch on and off this feature?
You are smart! thank you.. I like your content!
Thanks For lot.And Can I handover that VB code in notepad.
Really helpful,thanks
Thanks Randy for this. However, I can't get the label 'behind' the shape. I'm using this approach to create a rollover; however, due to this, whenever I try to click on that shape (to activate the macro associated with the shape), it ends up selecting the label. Any ideas ? I'm using excel 2007
Hello Ragan, thanks for liking the video. I have also ran into this issue in, 2007 so I used Excel 2010 to complete. I think that this was an issue Microsoft fixed on versions after 2012. I hope this helps. Please let me know if you have any additional questions.
Randy Austin Okay, I tested this on Excel 2010; there seem to be no issues with that. Probably an unresolved bug with the 2007 version. Thanks !
Very nice. Very good explanation. Thanks so much.
You are very welcome. Glad you enjoyed it
This video has been so helpful. But is it possible to create two buttons on the same sheet? I have two shapes that are hyperlinked to other sheets and I would like to use the hover over effect on them so that they light up when the cursor rolls over. I have been able to do one with the method described in the video and it works perfectly, but when I to add the code to the second one nothing happens and the first one stops working. I am not an excel expert, just a fiddler and so I don't understand the order of the coding too well. Any help will be appreciated. Thanks!
Hi thanks very much. I have a new channel and a new video which uses this technique (and much better sound quality) You can see it right here: th-cam.com/video/Oe-J3Y_Lbow/w-d-xo.html
Can you run this behind another application which is full screen so the macro’s on hoover still activate?
Hi and thanks, this only works within Excel when the application is active
Is there a way to make it work when it’s not active?
thanks alot dude
this is brilliant
Thank you so much! 💘
For sure you are very welcome
I try your code, and now I'm stuck on it whit label are not behind shape? Why it happen, I use Excel 2007? can you give me solution about my problems?
Very clever, pity that it needs an ActiveX Control, oh well, stuff Mac users, right?!
Yes not all Macs can work with ActiveX controls. I wish this weren't the case as well
its an awesome tutorial sir! Thanks a lot
Thanks so much. I really appreciate that.
Awesome!
New videos EACH WEEK with NEVER SEEN BEFORE Techniques in Excel with our Excel For Freelancers channel here: th-cam.com/users/ExcelForFreelancers
Brilliant
Thanks so much. Glad you liked it
thanks bro..
Your welcome. Happy to share