How do I clear the Excel VBA controls on my user form in one hit ?

Posted on 2013-10-13
Medium Priority
Last Modified: 2013-10-13
Hi Experts,

I have a user form with a number of controls including buttons.
One of the buttons is a CLEAR button which will clear each of the text boxes of any data. It currently does this as follows:

   coClientName = ""
   teClientAddress1 = ""
   teClientAddress2 = ""
   teClientAddress3 = ""
   teClientTown = ""
   teClientPostcode = ""

....and so on for the whole form.

My question is : how can I do this for the whole form in one line of code, rather than clear each control individually ?

Question by:Tocogroup
LVL 24

Accepted Solution

Steve earned 2000 total points
ID: 39568968
I do not belive that you can use a single line to clear all controls.
This would be because not all controls can be cleared.
The best I can think of is to clear all of a type by cycling all controls on the form.
This way you don't have to write the clear code for each control:

Dim ctl As Control
For Each ctl In Me.Controls
    If TypeName(ctl) = "ComboBox" Or TypeName(ctl) = "TextBox" Then
        ctl.Value = ""
    End If
Next ctl

Open in new window


Author Closing Comment

ID: 39568979
Thanks Barman, that's saved me a lot of unnecessary code.

Much obliged

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question