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]

Do not do this:
i populate the listbox with RowSource

Load the listbox with vba

 

[LongToast]

Do not do this:
i populate the listbox with RowSource

Load the listbox with vba

sorry i didn't get you, what you mean by "Load the listbox with vba?"

 

[My Aswer Is This]

Something like this:

Private Sub CommandButton1_Click()
'Modified  1/5/2022  6:48:00 PM  EST
ListBox1.List = Range("A1:A25").Value
End Sub

 

[LongToast]

Something like this:

Private Sub CommandButton1_Click()
'Modified  1/5/2022  6:48:00 PM  EST
ListBox1.List = Range("A1:A25").Value
End Sub

sometimes i have to update the value in that range, if i use ListBox1.List data in listbox not get updated, i have to re-load manually, but if i use RowSource, i only need to update data in sheet and listbox will auto update.

is it the only solution? i mean use ListBox1.List?

 

[My Aswer Is This]

So what is in the Rowsource?

Or try something like this:

Private Sub CommandButton1_Click()
'Modified  1/5/2022  6:54:00 PM  EST

With ListBox1
    .Clear
    .AddItem "Alpha"
    .AddItem "Bravo"
    .AddItem "Charlie"
End With

End Sub

 

[My Aswer Is This]

Show me what is in RowSource

 

[LongToast]

So what is in the Rowsource?

Or try something like this:

Private Sub CommandButton1_Click()
'Modified  1/5/2022  6:54:00 PM  EST

With ListBox1
    .Clear
    .AddItem "Alpha"
    .AddItem "Bravo"
    .AddItem "Charlie"
End With

End Sub

.RowSource = "'" & oWs.Name & "'!A" & lStart & ":" & "Q" & lFinish

i create something like pagination mechanism, my real data start from column A to Q in my sheet, for all i know, AddItem can only handle 10 column and i have 17 column, is it using array is the only option to make ListBox_Change and .ListCount > 0 working properly?

 

[My Aswer Is This]

I never realized you had a multicolumn Listbox.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[LongToast]

I never realized you had a multicolumn Listbox.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

oh ok sir, thanks by the way