Clear Values from All Controls on Form

ScottyBee · #1 03-28-2019, 10:42 AM

I have two types of controls on my form: textboxes and combo boxes. My text boxes are all named in the format txt1, txt2, txt3...... Combo boxes are cbo1, cbo2, cbo3....

I have a button on the form that clears all values from both controls. The button is named cmdClear. The code is:

Code:

Private Sub cmdClear_Click()
    Dim cControl As Control
    For Each cControl In Me.Controls
        If cControl.Name Like "txt*" Or cControl.Name Like "cbo*" Then cControl = vbNullString
    Next
End Sub

The first code block works just fine but I am trying to refine the code to work for all controls.

Code:

Private Sub cmdClear_Click()
    Dim cControl As Control
    For Each cControl In Me.Controls
        If cControl.Name Like "*" Then cControl = vbNullString
    Next
 End Sub

The second code block gives the following error:

"Run time error '5"
Invalid Procedure Call or Argument
Why am I getting this error?

I even tried:

Code:

Private Sub cmdClear_Click()
    Dim cControl As Control
    For Each cControl In Me.Controls
        cControl = vbNullString
    Next
 End Sub

This code generated the same error. I am just removing the If statement so all contols should be assigned a null value. Any ideas? Thanks

gmayor · #2 03-29-2019, 03:04 AM

Presumably you have other controls on the form such as the control button that cause the error. You could use an alternative approach e.g.

Code:

Private Sub cmdClear_Click()
Dim cControl As Control
    For Each cControl In Me.Controls
        Select Case TypeName(cControl)
            Case Is = "TextBox", "ComboBox"
                cControl = vbNullString
            Case Else
        End Select
    Next
End Sub

ScottyBee · #3 04-02-2019, 09:55 AM

Thanks gmayor, your code works great. Is interesting how easy it is for one to get caught up in the complexity of the code that we miss the simple things. Thanks again, I really appreciate it.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word Form to Save as and clear	sfitzsimmons	Word VBA	1	01-11-2019 02:03 PM
Don't clear form fields when protecting as a form	BruceM	Word	5	10-06-2016 08:26 AM
Form with content controls - expands but at the bottom of the form	louiseword	Word	3	05-27-2016 12:47 AM
Retrieve Values from Content Controls for hidden	john s.	Word VBA	1	02-21-2014 02:14 PM
Calculations using values from date picker controls	Inkarnate	Word	0	06-09-2010 07:16 AM