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: 30

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top