时间戳

首页 > 解决方案 > How to not clean a specific Textbox when other Userform controls are cleaned?

excel - How to not clean a specific Textbox when other Userform controls are cleaned?

问题描述

On my Userform, I have a textbox (let's say Txt1). I use it as a search bar for my listbox (Lst1).

When I write any expression to Txt1, I find what I am looking for inside Lst1. These two (Txt1 and Lst1) are integrated to my stock program.

When I search any product with Txt1, I can choose product name from Lst1.

When I choose the product from Lst1, I write quantity to Txt2 and press enter. It makes necessary process at the backside of the program.

When I press enter, Txt1 cleans itself.
I need to re-write new product name again to Txt1.

I want:
To not clean Txt1.

Txt1 Code :

Private Sub TextBox17_Change()

Dim i As Long
Me.TextBox17.Text = StrConv(Me.TextBox17.Text, 1)
Me.ListBox4.Clear
For i = 2 To Application.WorksheetFunction.CountA(Sayfa2.Range("C:C"))
    a = Len(Me.TextBox17.Text)
    If Left(Sayfa2.Cells(i, 3).Value, a) = Left(Me.TextBox17.Text, a) Then
        Me.ListBox4.AddItem Sayfa2.Cells(i, 3).Value
        Me.ListBox4.List(ListBox4.ListCount - 1, 3) = Sayfa2.Cells(i, 10).Value
    End If
Next i

End Sub

Lst1 Code :

Private Sub ListBox4_Click()

Dim i As Long, lastrow As Long
lastrow = Sheets("CDepo").Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
    If CStr(Sheets("CDepo").Cells(i, "C").Value) = (Me.ListBox4.Value) Then
        Me.TextBox2 = Sheets("CDepo").Cells(i, "A").Value
        Me.TextBox11 = Sheets("CDepo").Cells(i, "H").Value
        Me.TextBox9 = Sheets("CDepo").Cells(i, "C").Value
        Me.ComboBox2 = Sheets("CDepo").Cells(i, "B").Value
        Me.TextBox6 = Sheets("CDepo").Cells(i, "I").Value
        Me.TextBox13 = Sheets("CDepo").Cells(i, "J").Value
        Me.TextBox15 = Sheets("CDepo").Cells(i, "P").Value
        Me.TextBox8 = Sheets("CDepo").Cells(i, "D").Value
        Me.TextBox16 = Sheets("CDepo").Cells(i, "Q").Value
    End If
Next

End Sub

Commandbutton for Enter :

Private Sub CommandButton6_Click()

If UserForm11.ComboBox4 = "TesellumFisi" Then

    If TextBox2.Text <> "" Then
        Son_Dolu_Satir = Sheets("TesellumFisi").Range("A65536").End(xlUp).Row

        For i = 23 To Son_Dolu_Satir
            If Sheets("TesellumFisi").Cells(i, 2).Value = TextBox2.Text Then
                Exit Sub
            End If
        Next i
        Bos_Satir = Son_Dolu_Satir + 1
        Sheets("TesellumFisi").Range("A" & Bos_Satir).Value = Application.WorksheetFunction.Max(Sheets("TesellumFisi").Range("A:A")) + 1
        Sheets("TesellumFisi").Range("B" & Bos_Satir).Value = TextBox2.Text
        Sheets("TesellumFisi").Range("C" & Bos_Satir).Value = TextBox13.Text
        Sheets("TesellumFisi").Range("E" & Bos_Satir).Value = TextBox9.Text
        Sheets("TesellumFisi").Range("I" & Bos_Satir).Value = TextBox4.Text
        Sheets("TesellumFisi").Range("J" & Bos_Satir).Value = TextBox6.Text
        Sheets("TesellumFisi").Range("O13").Value = UserForm11.ComboBox1.Text
        Sheets("TesellumFisi").Range("H" & Bos_Satir).Value = UserForm11.ComboBox2.Text
        Sheets("TesellumFisi").Range("G" & Bos_Satir).Value = TextBox8.Text
    End If
End If

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

'--
TextBox2.SetFocus
TextBox2.SelStart = 1
TextBox2.SelLength = Len(TextBox2.Text)

End Sub

标签： excelvbatextbox

解决方案

To skip over Text1 but clear the rest of the text boxes you need a conditional in your controls loop.

Dim TC As Control
    For Each TC In Controls
        If TypeName(TC) = "TextBox" Or TypeName(TC) = "ComboBox" Then
            If Not TC.name = "TextBox1" then 'Assuming this is the right name
                TC.Value = ""
            end if
        End If
    Next TC
    Set TC = Nothing

推荐阅读