loop controls in userform except one textbox

Hello there,

I am using a userform where i have my textboxes, comboboxes with a commandbutton.
When i click on that commandbutton, i would like to reset all the textboxes except one of them.
Currently i am using the following code i got from the internet. But how can I have one of my textboxes unchanged on the userform?

Private Sub cmdAdd_Click()

Dim cCont As Control

        For Each cCont In Me.Controls

            If TypeName(cCont) = "ComboBox" Then

            cCont.Value = ""

            End If
            
            If TypeName(cCont) = "TextBox" Then
            
            cCont.Value = ""
            
            End If

            If TypeName(cCont) = "Label" Then

            cCont.Value = ""

            End If
            
        Next cCont

End Sub

Thanks

gkisacik

Hi gkisacik,

please use CODE tag around your code.

Go to post -> Edit -> Go advanced -> select your code and use # sign around it

Use the TextBox Tag Property, give the TextBox to exclude a value then this code will work

Option Explicit

Private Sub cmdAdd_Click()

    Dim cCont As msforms.Control

    For Each cCont In Me.Controls
        With cCont
            Select Case TypeName(cCont)
            Case "ComboBox": .Value = ""
            Case TypeName(cCont) = "TextBox"
                If .Tag <> "" Then .Value = ""
            Case "Label": .Caption = ""
            End Select
        End With
    Next cCont
End Sub

Why not try the code,add anything to the Tag property of txttrial, say x. Then run the code

What are you asking?

txtpercentage1 = "", txtpercentage2 = "", txtpercentage3 = ""............

txttrial = "some text"

If so BOTH variations of the code work.

Are you wanting to exclude other controls, for instance Labels?

EDIT:

Posted before I saw your code.

Your code as supplied has two If statements with no End If

I have no idea where this should be fixed, only you know that

Change the code block to

    Dim cCont As msforms.Control
    Dim i As Integer
    
    For i = 0 To Me.Controls.Count - 1
        Set cCont = Me.Controls(i)
        With cCont
            Select Case TypeName(cCont)
                Case "ComboBox": .Value = ""
                Case "TextBox"
                    If .Tag <> "x" Then .Value = ""
                Case "Label"
                    If .Name = "lblproduct" Then .Caption = ""
                End Select
        End With
    Next

Depending on where your If statements end, you may not need these two lines

        Me.txttotalweight.Value = ""
        Me.txttotalpercentage.Value = ""

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

The reason that I suggested using the Tag property is that it is easier to maintain, you can add the exception to other TextBoxes or remove it easily this way.

Also,Select case is much easier to follow.

I pasted incorrectly in the first post, my code should be

Private Sub CommandButton1_Click()
 Dim cCont As msforms.Control

    For Each cCont In Me.Controls
        With cCont
            Select Case TypeName(cCont)
            Case "ComboBox": .Value = ""
            Case "TextBox"
                If .Tag = "" Then .Value = Empty
            Case "Label": .Caption = ""
            End Select
        End With
    Next cCont
End Sub

I can't really see why you need to clear labels

Well spotted Marcol

No problem