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! :)
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.
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.
@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.
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.
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.
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!
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.
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.
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.
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.
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🙂
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?
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..
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 :-)
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 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 😉
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
Hi Laila u r a grt teacher indeed, ur each video teaches new things. Thankx
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
Thanks!
Thank you Manuel. Appreciate it.
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
I admire your work, Thanks
Nicely done Leila! Great explanation of how your vba works.
Thanks Kevin! Glad to hear you like the explanation.
Enjoying all your videos, learning great stuff, and more importantly, using in everyday business :-)
That's what I love to hear:)
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!
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.
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!
Oh lord! VBA. Scary. 😱
Brilliant content. 👍
You mean scary fun right? :) Thanks for dropping by Oz. See you soon in person :)
-removes "proficient" from excel on my resume....
the more I learn the more I realize I know NOTHING about excel lmao. This is amazing.
Thank you for the video and the workbook...
Finally VBA , thank you for sharing 👌🏻
:) yes finally... more to come soon.
Awesome, Leila, thank you :)
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.
This is awesome.
Glad you like it.
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!
Thank you! Very good job!
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.
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.
Very great Idea, thanks.
You're very welcome Ali. Thanks for dropping by.
You are awesome Leila ...I love to watch your videos :)
That makes me really happy! Thank you for watching & for the nice comment :)
Great idea .. really awesome...
Thanks Sukhomoy :)
Tried to apply this to map but no matter what formats I used, jpg, png,....no one is working.....Any ideas?
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/
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.
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🙂
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.
Awesome!!
Thanks Hashim. Glad you like it!
Just amazing :-)
Thanks! Glad you like it.
Your great! thanks
You're very welcome Jeff. Glad you like it!
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..
Thanks Leila
You're welcome John.
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.
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 :-)
magique !
Magic & fun :)
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
Whatif I type 120% there?!
thanks thanks thanks
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 😉
Thank you madam
You're very welcome. Thank you for dropping by.
নাইছ ভিডিও
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.
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
Hii leila