Clear TextBox value

[LongToast]

i had code like this :

Private Sub ListBox1_Change()
    With Me.ListBox1
      
        If .ListCount > 0 Then
            Me.TextBox2 = .List(.ListIndex, 0)
            Me.TextBox5 = .List(.ListIndex, 1)
        End If

    End With
End Sub

i populate the listbox with RowSource, when listbox has data, those textboxes get value based on selection in listbox and it works perfectly, but when i re-populate the listbox and the listbox has NO data, those textboxes still show data before re-populate instead empty value or nullstring, i had try this :

Private Sub ListBox1_Change()
    With Me.ListBox1
      
            Me.TextBox2 = vbNullString
            Me.TextBox5 = vbNullString

        If .ListCount > 0 Then
            Me.TextBox2 = .List(.ListIndex, 0)
            Me.TextBox5 = .List(.ListIndex, 1)
        End If

    End With
End Sub

but didn't works, it's like ListBox1_Change event not working properly, can someone explain this please.

thanks in advance.

 

[My Aswer Is This]

Or try something like this:

Private Sub CommandButton1_Click()
'Modified  1/5/2022  7:19:00 PM  EST
ListBox1.ColumnCount = 2
ListBox1.List = Range("A1:B25").Value


End Sub

 

[My Aswer Is This]

Not sure what this means:
.RowSource = "'" & oWs.Name & "'!A" & lStart & ":" & "Q" & lFinish

Tell me in words what this means:

Are you showing a sheet name and the range

So tell me in words the sheet name and the Range

 

[LongToast]

Not sure what this means:
.RowSource = "'" & oWs.Name & "'!A" & lStart & ":" & "Q" & lFinish

Tell me in words what this means:

Are you showing a sheet name and the range

So tell me in words the sheet name and the Range

that is for load data to listbox, i create pagination mechanism in my form, so user can click what ever page they want and that code will bring to them the data based on the page number, i create formula for that (to count where row to start and where row to finish) example : if user click page 2, then formula to count lStart and lFinish will automatic give row 26 and 50 (since my data start from row 2, row 1 is header, and i give them limitation to 25 data / page) and oWs name is name of worksheet i use to save data.

 

[My Aswer Is This]

Thanks for that explanation even if I do not understand it.

I'm sure someone else here on the forum may be able to help you.
I will keep watching. Take care.

 

[LongToast]

Thanks for that explanation even if I do not understand it.

I'm sure someone else here on the forum may be able to help you.
I will keep watching. Take care.

i use oWs.Name instead "Sheet1" or "Sheet2" because i need it to be dynamic, so data for listbox not only from one specific sheet, it can be from anywhere.

 

[My Aswer Is This]

i use oWs.Name instead "Sheet1" or "Sheet2" because i need it to be dynamic, so data for listbox not only from one specific sheet, it can be from anywhere.

That's why I would need to see all your code.

Like you said this:
i populate the listbox with RowSource

How do you do that?
Do you go into the Properties window and modify it there?

 

[LongToast]

Public Sub LoadListBox(ByRef oList As MSForms.ListBox, ByVal iLimit As Byte)
    Dim oWs As Worksheet
    Dim lRow As Long
    
    Set oWs = ThisWorkbook.Sheets(strSheet)
    
    lRow = oWs.UsedRange.Rows.Count
    
    oList.RowSource = vbNullString
    
    If lRow > 1 Then
        Dim lStart As Long
        Dim lFinish As Long
        
        lStart = iPage * iLimit + 2 - iLimit
        lFinish = lStart + iLimit - 1
    
        If lRow < lFinish Then lFinish = lRow
            
        With oList
            .ColumnCount = 17
            .ColumnHeads = False
            .ColumnWidths = "120,120,200,0,0,0,0,0,0,0,0,0,0,0,0,0,0"
            .RowSource = "'" & oWs.Name & "'!A" & lStart & ":" & "Q" & lFinish
            .Selected(0) = True
        End With
    End If
    
    Set oWs = Nothing
End Sub

strSheet is public variable (string) to handle changing of sheet in use.
iPage is public variable (integer) to handle what page is user click.

 

[LongToast]

That's why I would need to see all your code.

Like you said this:
i populate the listbox with RowSource

How do you do that?
Do you go into the Properties window and modify it there?

so, any help sir?

 

[LongToast]

since ListBox_Change event and "If ListBox.ListCount > 0" not automatically clear my textboxes from the data before re-populate, it's seems i have to do manually clear it with TextBox = VbNullString, for 17 text boxes.

i will let this thread unsolved btw.