Excel Training VBA 2 Lesson 7

Filling a ListBox and ComboBox Using Loops

Information Helpful? Why Not Donate | Free Excel Help >> Excel Training-Video Series >> Build Trading Models In Excel
<<Previous Lesson | NEXT LESSON>> | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX

Filling a ListBox and ComboBox Using Loops

While using the RowSource Property referring to a range is the most likely way to fill a ListBox and ComboBox and probably the easiest way. There are times when the AddItem method is a better option and in some cases the only option. Let’s say that you want to fill a ComboBox or ListBox with times that cover the previous 24 hour period at 15 minute intervals. You could of course use the NOW() Function on a hidden Worksheet and increment it by the needed increment, ie;

A1 = NOW()

A2 = A1+0.0104166666666667

A3= A2+0.0104166666666667

And so on…… But as you can see this can possibly get a bit messy, so let’s use a Loop instead.

This Loop will fill our ListBox with times that are incremented at 15 minute intervals over a 24 hour period. The code will only run when the UserForm that houses the ListBox is initialised. We could of course have the code run via a CommandButton or many other methods.

Summary

So as you can see there does need to be some foresight as to which Control is best suited to a particular situation. At times you may even use both the ComboBox and ListBox. Whenever possible try and use the RowSource Property to fill your Control as it is much easier to add, modify and change the list.

Information Helpful? Why Not Donate | Free Excel Help
<<Previous Lesson | NEXT LESSON>> | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX