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

riyajugen

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This is Listbox Loading when Form Initiate:

VBA Code:
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:

VBA Code:
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
 

Attachments

  • Sort_Error.jpg
    Sort_Error.jpg
    172.2 KB · Views: 4

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,292
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
 '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
 
Solution

riyajugen

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,118
Messages
5,576,202
Members
412,706
Latest member
msousa25
Top