How to Customize Controls in Excel VBA

Подписаться 84 тыс.

Просмотров 12 тыс.

50% 1

How to Customize Controls in Excel VBA
In this video, I show you how you can customize controls on UserForms.
If a control does not have the behavior you like then you can easily add it. The best part is that once you have created this behavior you can apply it to any control you wish with just a couple of lines of code.
The see the benefits of customizing controls I will show you a real-world example that I created and has been employed by hundreds of VBA users.
#excelvba #VBACustomEvents #VBAWithEvents #WithEvents #UserFormControls
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
Learn how to build Excel VBA Applications the right way using best practices and proven techniques:
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
DOWNLOAD the Code for this video: shorturl.at/pN179
(If there is a problem with the download page make sure to turn off any AdBlockers that are running)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + L OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)

Наука

Опубликовано:

20 дек 2023

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 36

@Trucpq 6 месяцев назад

Thanks a lot. Class Module is so strong but not many person can explain as good as you.

@wizardofaus8473 5 месяцев назад

Thanks Paul. You are one of my favourite teachers. I consider myself fortunate that you happen to teach a topic I have great interest in.

@Excelmacromastery 5 месяцев назад

Thanks😀

@k0023382 6 месяцев назад

Excellent, I wish you can do more real samples like this one using Classes in the future, I have noticed most tutorials cover very simple examples with no real /practical use.

@Excelmacromastery 6 месяцев назад

Glad you liked it.

@iincitr 5 месяцев назад

Again, this is an excellent explanation. I like every piece of information that you share. Thank you.🙂

@Excelcopilot7 5 месяцев назад

Great video!! Thank you. I am happy to use VBA.

@mmbcampus 5 месяцев назад

This is awesome. Great man... I really enjoy your video... Basic fundamental explanations from scratch, then project build-up...

@danieladeti4149 5 месяцев назад

Well Explained

@claudiomacamo1396 5 месяцев назад

Very useful. Thank you

@astollie 5 месяцев назад

Nice video. I like the stepped and structured way you explain advanced functions like withevents and classes. The demonstration in a practical application makes it even more stimulating to take the unsure step into trying something new that once mastered is so helpful. Great work! thanks.

@jackmacdonald8499 6 месяцев назад

Paul - I normally use MS Access for my development, but I often follow your RU-vid channel. By coincidence, the online Access Users Group recently had two demonstrations of WithEvents, so I was intrigued by your video. It took me a while to understand how to create an Excel User Form, but once I had it working, I was highly impressed by your work. I intend to see if I can implement the same concept in my Access projects. Thank you for posting.

@Excelmacromastery 5 месяцев назад

You're welcome Jack.

@kumarsharmaajit4811 4 месяца назад

wow! this is something worth learning! :)

@Rapture1469 6 месяцев назад

Nice! You are at another level with your code.

@Excelmacromastery 6 месяцев назад

Thanks

@martinlenaerts 5 месяцев назад

Incredibly useful video. I've heard of using Withevents before, but your explanation somehow makes it more accessible and understandable. What I understand (but you did not explain explicitly) is, that the method you present does not inhibit the normal event calls in the userform itself. I've been developing a userform application that has a lot of textboxes, comboboxes etc. So I decided to add a Help-textbox at the bottom of the userform that displays dedicated helptext. For this, I use the even _Enter(). That's indeed a lot of code that I can now replace with the class withevents. Thanks so much!

@Excelmacromastery 5 месяцев назад

Glad it was useful Martin.

@peterwooldridge7285 6 месяцев назад

Now that's a nice Xmas present...Cheers Paul

@Excelmacromastery 6 месяцев назад

You're welcome

@edgarrocha9536 5 месяцев назад

Great Video! So Usefull. A Suggestion for future videos is show some examples with custom controls with RaiseEvent method to create custom events, and how to deal with it in Form itself, when you use an array of objects.

@Excelmacromastery 5 месяцев назад

Thanks. I debated including Raise events but in the end I thought it might lead to confusion. I may do a video on this topic in the future.

@user-vg1zf8dn5m Месяц назад

Hello, thank you for the video. I need help, please What should we do? If the site requires you to log in first, then scrape the data from it after that

@adalbertdudziak1792 5 месяцев назад

Hi, great tutorial! Is it possible to return to a class the name of the control that initialized this class?

@hammeedabdo.82 5 месяцев назад

I am interested in algorithmic trading with VBA. Any tips? Do you know any websites or forums that focus on algorithmic trading with VBA that you would recommend?

@AbdulRahmanSABRA 5 месяцев назад

Dear Paul , I usually use collection of Textboxes in Userform , my question is there any difference between Array and collection ?

@MagicOfMushrooms 4 месяца назад

I love this and all your videos. Question, how did you add that "Statistics" button at around 15:55?

@Excelmacromastery 4 месяца назад

MZ Tools

@ragnarok7976 6 месяцев назад

Keep in mind that class that uses withevents must maintain its reference or the events will not fire. If you had declared the class locally in a method then the event fires after that method exits the reference will be destroyed and the code you've bound to that event will not fire. This has caused me hours of confusion and troubleshooting in the past so I figured I'd share it here.

@Excelmacromastery 6 месяцев назад

Very true. That's why it is better to declare at the top of a UserForm so it can be used throughout that UserForm's lifetime.

@ragnarok7976 6 месяцев назад

@@Excelmacromastery Your code is great as usual. My only worry was that people will likely not be copying it verbatim but rather adapting the principles to their own project. In my case I was making a unique toggle button group and after adding the buttons to the group I knew I would never need to explicitly reference the group again but little did I know that these proxies are implicitly referenced and these "lite references" are not enough to keep the proxies from being destroyed when they go out of scope.

@Simplifiedsurveying Месяц назад

How do you then access the properties of another For example of I wanted the user to type in textbox2 and the value appears in textbox1. I want to use similar experience, I created buttons when a user hovers over it, it changed color and when exits it goes back to the original color, I wrapped a textbo around the button to capture mouse movement when the user exits the button but this textbox can't change the properties of the button when in class module

@mihalydozsa2254 Месяц назад

I love your videos, but the searchable combobox could be done simpler. I know why you did it this way. But if you use a dictionary to filter and make the default list that way it does not glitches out and it is still fast enough, even with 30000 rows. And you have a combobox with all unique entry. And it is scalable to multiple combobox.

@hammeedabdo.82 5 месяцев назад

Do you think there will still be a demand for VBA programmers in the coming years, especially with the changing landscape and the rise of AI?

@Excelmacromastery 5 месяцев назад

Yes. Even if VBA was replaced today there is still hundreds of millions of lines of code to be supported. That said there is no replacement for VBA right now. * Office Script can only do a fraction of what VBA does and it requires using Power Automate for events. * Python requires third party software to connect to the object model and installation can be problematic.