Excel VBA Clear all controls on a Userform

Creating userforms in Excel to carry out basic or even complex tasks is relatively straightforward. A bit of creativity and inspiration is required as well as a little bit of Excel VBA.

Datanology build userforms into the majority of our projects and they are a vital ingredient in any Excel based database system you might wish to create. Although they may look very grey and dull in the first instance, how you deal with that is as important as the code itself that drives it’s functions.

This blog shows how easy it is to have a stored procedure kept in one of the userforms modules to call upon when you want to instantly clear all the controls (textboxes, comboboxes, option buttons etc) of any values or choices – essentially start again with a blank canvas. This is particularly useful in database projects where you may want to add a new record but the previous one must be gone with no trace of the data around to contaminate the new record.

With the example below, you can incorporate all or part of it into the userforms codes, attached to a button or triggered by double clicking something, or as a stored procedure.

It makes no difference how you have named your textboxes, comboboxes etc, this will just clear all the controls by type rather than by name. Whilst this may be the best and easiest option for clearing all the controls on a userform, there are other ways of doing so which are covered in other parts of this reference blog. You can use the search button to find what you are looking for.

Feel free to ask us a question, or leave a comment below and we’ll answer the question for everyone to see and learn from, maybe even us !



Public Sub Clear_ALL_Controls()
Dim ctl As MSForms.Control
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "CheckBox", "OptionButton", "ToggleButton"
ctl.Value = False
Case "ComboBox", "ListBox"
ctl.ListIndex = -1
End Select
Next ctl
End Sub






Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

]
Datanology

Leave a Reply

Your email address will not be published. Required fields are marked *