Turning Basic Excel into Dynamic Word Art Masterpieces with VBA
HTML-код
- Опубликовано: 21 авг 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.c...
Discover the power of VBA in Excel for customizing Word Art! In this easy-to-follow tutorial, we dive into the fascinating world of VBA, specifically focusing on conditionally formatting Word Art. Whether you're working on a project or tracking progress, this video is your go-to guide for visualizing percentages in a creative and clear way.
⬇️ Download the workbook here: pages.xelplus....
🔑 Key Highlights:
Explore how to link Word Art to cell values for dynamic updates.
Learn the step-by-step process to manipulate Word Art properties like fill color and gradient using VBA.
Understand the core concepts of VBA - objects, properties, and methods - in a straightforward, practical manner.
Discover how to find the right VBA syntax for your needs, even if you're not a frequent VBA user.
🎓 Get access to the complete course here 👉 www.xelplus.co...
📌 What You'll Learn:
Connecting Word Art to Cell Values: We show you how to make your Word Art reflect changes in cell values, perfect for visually tracking data like project completion percentages.
Manual Formatting vs. VBA: Compare the limitations of manual formatting and the vast possibilities with VBA.
Real-Time Problem Solving: Witness the process of solving a user's query from an Excel forum, providing insights into practical VBA application.
VBA Syntax Discovery: Uncover the methods to determine the right VBA syntax for specific tasks, a valuable skill for any Excel user.
Final Touches with Macro Recorder: Learn how to use the macro recorder effectively to kickstart your VBA coding.
Find out how you can use Excel Macros to control the fill color of Wordart in VBA. In this specific case we'd like to show percentage completion as WordArt and control the amount of the fill color based on the actual percentage. If we have 50%, only half of the 50% word will be highlighted.
This video is more than using Excel VBA to control word art. I'll show you how you can find the right syntax, even when the macro recorder doesn't work and doesn't pick up the right code for you. I'll show you how to use the VBA object library to find the correct syntax and how to use Excel's event procedures to connect the macro to your cell value.
🌍 My Online Excel Courses ► www.xelplus.co...
If you'd like to learn more and become more comfortable with VBA, check out my complete course here: courses.xelplu...
★★ Links to related videos: ★★
Video #1 using bar-in-bar chart: • Show Progress Using an...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/vba-wordart-file
Thank you so much, Leila, for this video. I particularly enjoyed the part where you explain how to use the object library. Grateful for that!
You're very welcome Celia. Glad you like the video. Yes - the object library can be really helpful. Funny thing is, when I started to learn VBA, I didn't even know it existed for the longest time :)
Same thing here, Leila! I've been working with VBA for over 18 months now and I knew it existed, but I never used it because I wasn't aware of its interactivity with the code you're writing. I can't wait for the first real opportunity to try this out! :)
👍
Leila Gharani i
Once again a Big Salutation to my beloved and beautiful Excel Teacher/Guru, for explaining fantastically with VBA. In every video you are putting your heart & soul, in doing miracles. I am not missing any of your videos. My humble request to you Madam, please start the series VBA. Thanks a lot for doing this with keen interest and with great enthusiasm.
Thank you so much Gopala and also for not missing any videos :) This encourages me to keep going. I will be starting the VBA series with some basic videos in the next month.
We are waiting for vba classes as well thanks alot mam
Thank You Madam & eagerly waiting to watch and learn.
ms Leila wow you are really great your teaching way is very unique.👍👍👍👍👍👍👍👍👍👍
Glad you like it. Thanks for the thumbs up!
@@LeilaGharani 👍👍👍👍👍👍👍👍👍👍
@@LeilaGharani plz stay safe stay home
-removes "proficient" from excel on my resume....
the more I learn the more I realize I know NOTHING about excel lmao. This is amazing.
Hi Laila u r a grt teacher indeed, ur each video teaches new things. Thankx
Another great demonstration of an Excel 'hack', Leila. Really looking forward to the VBA course you are releasing soon!
Thanks Andrew! Looking forward to seeing you in the course :)
All your tutorials are great. Thanks Laila
Nice idea and well explained.
Colour constants from "VBA.ColorConstants" can be used for 8 basic colours instead of RGB() function, e.g. vbBlack, vbWhite or vbYellow. Preset named RGB colours are available from 'XlRgbColor' enumeration, e.g. rgbBlack.
I recommend using full qualifier to any object to make sure the object is indeed the one intended and not expect Excel to guess for you. In your video you removed 'ActiveSheet' from the 'Shapes' collection however this entails that when Excel triggers the 'Change' procedure, you must be darn sure that the 'Shapes' collection is actually pointing to the proper sheet. In many years of programming VBA I've often encountered code where the author was overconfident about the object's context and did not use the full hierarchy but in practice Excel was addressing an object of the same kind but located elsewhere and the code would fail.
Nicely done Leila! Great explanation of how your vba works.
Thanks Kevin! Glad to hear you like the explanation.
I admire your work, Thanks
Enjoying all your videos, learning great stuff, and more importantly, using in everyday business :-)
That's what I love to hear:)
Oh lord! VBA. Scary. 😱
Brilliant content. 👍
You mean scary fun right? :) Thanks for dropping by Oz. See you soon in person :)
Thank you so much! Please come up with more videos ! From Singapore
You're very welcome. For sure. Your support helps with keeping this channel going. Thank you!
Finally VBA , thank you for sharing 👌🏻
:) yes finally... more to come soon.
Thank you for the video and the workbook...
That was Great Leila. Love your work and simplicity of explaining things. Thanks for sharing file too..
You're very welcome Jay!
I dont mean to be so off topic but does someone know a trick to get back into an Instagram account?
I stupidly lost the login password. I appreciate any tricks you can give me.
@Felix Martin Instablaster =)
@Rodney Julian thanks for your reply. I found the site on google and im waiting for the hacking stuff now.
Seems to take quite some time so I will reply here later with my results.
@Rodney Julian it did the trick and I actually got access to my account again. Im so happy:D
Thanks so much, you saved my ass!
You are awesome Leila ...I love to watch your videos :)
That makes me really happy! Thank you for watching & for the nice comment :)
Awesome, Leila, thank you :)
Thank you! Very good job!
I really like the video. im waiting for the VBA course to out, i really hope it will come soon thank u.
I always learn something new from the ur video
Thanks Vikas. Glad to hear you learn something new. I'm working really hard on this course. It's going to be one of my longest and most comprehensive courses to date. Looking forward to seeing you inside the course.
No rush please. We all can wait, till course is compleeted. We all know that it will be moast powerful course. So take Your time and finish Your masterpiece. Thanks for knowledge!
Thanks!
Thank you Manuel. Appreciate it.
Very great Idea, thanks.
You're very welcome Ali. Thanks for dropping by.
This is awesome.
Glad you like it.
Mind blowing i always have problems with VBA i don''t really know why it's still not understandable for me, anyway thank you so much for the video it was so powerful.
You're welcome Ismail. VBA is a bit difficult to get the hang of....at least it was for me :) With VBA it's important to understand the basics before digging in. Otherwise it can get quite frustrating. Once it starts to make sense, it can be quite fun and really helpful.
Great idea .. really awesome...
Thanks Sukhomoy :)
Hello Leila, you are awesome. Thanks a lot for making a significant change in my career. When is your VBA course coming on.
Thank you Robert for your support too. I'm very happy to hear you find the videos useful for your work! VBA should be hopefully out by the end of March.
Awesome!!
Thanks Hashim. Glad you like it!
thanks thanks thanks
Your great! thanks
You're very welcome Jeff. Glad you like it!
Just amazing :-)
Thanks! Glad you like it.
Very useful…thank you so much…I have a query though…what if value exceeds 100%…lets say 120%…I am sure entire figure will be filled with Black…what I want is additional information popping up besides the word art stating value exceeded 100%…some sort of alarm/indicator..can be handy if we are measuring expenses against budget. Thank you in advance as I know you’ll come up with solution very soon :-)
নাইছ ভিডিও
Hey Leila,
I have been your subscriber from quiet a long time and would like to appreciate the effort you put in each of your video. I have even recommended your videos in my office bcoz they are really helpful. I just have a small request. Can you please create a video on text formulas wherein I can extract Nth word from a sentence. For example, there is a complete address and from the address I need to extract the state which is in between of the address. Each word separated by comma.
It would be of great help if you can help us in improving our custom text formulas.
Thank you so much for putting so much effort and hardwork in creating such helpful videos.
Hi Mohammad - thank your for your support and recommendations! Really appreciate it. Sure - I will add this topic to my list. To separate words that fall between commas, you can also use text to columns feature. Or are you looking for a formula solution?
Leila Gharani
Thanks for your acknowledgement. Yes I am looking for a formula solution.
Ok great. Thanks for the feedback. Have added this to my list.
Thanks Leila
You're welcome John.
I think there is an easier way for it if you create bar graph at the back of your wordart, and make the wordart fill no fill, so that it will see the bar graph when you change the value 😉
magique !
Magic & fun :)
Such a great tutorial. I was wondering how you would apply conditional formatting to the colour. For instance green for a percentage of 70%-100%. I don't know if that can be done. Appreciate any advice.
You can do this by adding an extra "If" statement in the VBA just before you set your gradient stop 1 colour. Something like this:
If Target.Value >= 0.5 Then
.GradientStops(1).Color = RGB(51, 153, 51) ' Green
Else
.GradientStops(1).Color = RGB(0, 0, 0) ' Black
End If
In this example, the colour is green if the percentage is 50% or more. If you're a little more experienced with VBA, you could also create a Select Case to set the colour based on multiple percentages.
Looking forward to your VBA course. Will it be on Udemy?
Thank you. Yes - the course will be on Udemy. It's been the longest & the most comprehensive course I've worked on so far... I'm curious to get your feedback once it out....
I'd be very happy to. Thanks
Thank you madam
You're very welcome. Thank you for dropping by.
oh my god what Magic
Excel + VBA = magic :)
I am from Bangladesh follow your every post about MS Excel But no fortune to subscribe because of no Credit card Facility. I am also great
When you had it 100% and changed to text it maintained the black, whai if it was 50% and typed a text? Will it maintain that half fill?
Sorry I can't try it myself I don't have excel.
And can we have gradient kind of fill? Example..if achievement is upto 75% it remains on green side…beyond 75% till 90% it turns amber…and above 90% it turns into red..
Tried to apply this to map but no matter what formats I used, jpg, png,....no one is working.....Any ideas?
if don't mind can you clarify With Shapes("Rectangle4").TextFrame2.TextRange.Font.Fill
That's the object hierarchy to get to the fill color of the font object. The macro recorder can help get the object hierarchy right in most cases.
GREAT JOB. I have a before save event to check all required cells have data. but when I click X to close the sheet, I get the save message box and when I clik save of save as, it close the worksheet and save. I want to when I get the save message box to call for before save event. How to do that? Thanks in advance.
Hi - this post might help give you an idea: spreadsheetpage.com/index.php/tip/handling_the_workbook_beforeclose_event/
Hello dear, please help this condition suppose a column 10 unique name and every name colour different different if 11 name repeated in 10 unique name then automatically colour on first time but how to be possible at once colour in excel I m says like grouping text different different colours in excel please help🙂
Hii leila
Whatif I type 120% there?!
Thank u but I need a source for learning coding
If you're interested I do offer a VBA course that takes you from beginner to expert.