Error: "Permission Denied - 70" showing while loading Array value into Listbox in Userform while sorting button click.

[riyajugen]

This is Listbox Loading when Form Initiate:

Sub LoadDataList()
On Error Resume Next
Dim oLastRow As Long
Dim oMatchFoundIndex As Long
Dim oCounter As Long

    With oCurrentSheet
        oLastRow = .Range("A65000").End(xlUp).Row
        lstData.RowSource = .Range("A3:B" & oLastRow).Address(external:=True)
    End With
On Error GoTo 0
End Sub

This is Sort Button Click Event:

Private Sub cmdSort_AtoZ_Click()

    Dim i As Long
    Dim j As Long
    Dim sTemp As String
    Dim sTemp2 As String
    Dim LbList As Variant
    
    'Store the list in an array for sorting
    LbList = frmProductCategory.lstData.List
    
    'Bubble sort the Array
      'Bubble sort the array on the first value
    For i = LBound(LbList, 1) To UBound(LbList, 1) - 1
        For j = i + 1 To UBound(LbList, 1)
            If LbList(i, 0) > LbList(j, 0) Then
                'Swap the first value
                sTemp = LbList(i, 0)
                LbList(i, 0) = LbList(j, 0)
                LbList(j, 0) = sTemp
                
                'Swap the second value
                sTemp2 = LbList(i, 1)
                LbList(i, 1) = LbList(j, 1)
                LbList(j, 1) = sTemp2
            End If
        Next j
    Next i
    
    'Repopulate with the sorted list
    Me.lstData.List = LbList
On Error GoTo 0
End Sub

While clicking that Sort button (Refer Image), got an error like "Permission Denied - Error Code: 70"

Kindly anyone help me to solve this issue

 

[dmt32]

Hi,
as you want to re-populate your listbox after the bubble-sort by using the List property of the control, I suspect you will need to first disconnect the RowSource

 'Repopulate with the sorted list
    With Me.lstData
        .RowSource = ""
        .List = LbList
    End With

do note that In doing this, you will lose the displayed Headers in the listbox.
If you want to retain headers in listbox & continue to use RowSource to populate the control then I suggest that you look at sorting the range.

Hope Helpful

Dave

 

[riyajugen]

Thank very much, Coding working.

But Header disappeared as you said, Pls help one more time for retain header again for each sorting.

Thanks in advance.