Permission Denied error when I try to run my code

2020Rivalry

New Member
Joined
Apr 12, 2022
Messages
35
Office Version
  1. 365
Platform
  1. Windows
I created a userform recently, inside of it got 14 columns. Now I want to use a textbox to filter the data based on one single column. But the error message: Permission Denied keep popping out. Any helps?

Thank you very much!

VBA Code:
Private Sub txtSearch_Change()

    Dim myList() As Variant
    Dim X As Long
    Dim Y As Long
    Dim FoundSomething As Boolean
    Y = 0
    For X = 6 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
        If InStr(1, UCase(Sheets("Sheet1").Range("A" & X).Value), UCase(txtSearch)) > 0 Then
            FoundSomething = True
            ReDim Preserve myList(Y)
            myList(Y) = Sheets("Sheet1").Range("A" & X).Text
            Y = Y + 1
        End If
    Next
    
    If FoundSomething Then
        ListBox.ColumnCount = 14
        ListBox.ColumnHeads = True
        ListBox.ColumnWidths = "55,220,100,100,70,70,70,55,130,80,80,70,150,150"
       [COLOR=rgb(250, 197, 28)][B] [/B][/COLOR][COLOR=rgb(71, 85, 119)][B]Me.ListBox.List = myList()[/B][/COLOR]
    Else
        Call Refresh_data
    End If

End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
That's probably because the RowSource property for your listbox has been assigned a value. You'll need to remove that value from its property.
 
Upvote 0
That's probably because the RowSource property for your listbox has been assigned a value. You'll need to remove that value from its property.
ya, I forgot to attach the Userform_Activate code
VBA Code:
Private Sub UserForm_Activate()

    Call Refresh_data
 
End Sub

VBA Code:
Private Sub Refresh_data()
    
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    Dim le As Long
    lr = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    If lr = 5 Then lr = 6
    
    With Me.ListBox
        .ColumnCount = 14
        .ColumnHeads = True
        .ColumnWidths = "55,220,100,100,70,70,70,55,130,80,80,70,150,150"
        .RowSource = "Sheet1!A6:N" & lr
    End With
        
End Sub

VBA Code:
Private Sub txtSearch_Change()

    Dim myList() As Variant
    Dim X As Long
    Dim Y As Long
    Dim FoundSomething As Boolean
    Y = 0
    For X = 6 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
        If InStr(1, UCase(Sheets("Sheet1").Range("A" & X).Value), UCase(txtSearch)) > 0 Then
            FoundSomething = True
            ReDim Preserve myList(Y)
            myList(Y) = Sheets("Sheet1").Range("A" & X).Text
            Y = Y + 1
        End If
    Next
    
    If FoundSomething Then
        ListBox.ColumnCount = 14
        ListBox.ColumnHeads = True
        ListBox.ColumnWidths = "55,220,100,100,70,70,70,55,130,80,80,70,150,150"
        ListBox.List = myList
    Else
        Call Refresh_data
    End If

End Sub

how can I edit my code such that it works.....
 
Upvote 0
Do you still want all 14 columns in the listbox, or just the 1st?
 
Upvote 0
Ok, how about
VBA Code:
Private Sub textbox1_Change()

    Dim myList As Variant, Rws As Variant
    Dim FoundSomething As Boolean
    With Sheets("Sheet1")
      With .Range("A6:N" & .Range("A" & rows.count).End(xlUp).Row)
         Rws = Filter(.Worksheet.Evaluate(Replace("transpose(if(isnumber(search(""" & Me.TextBox1.Value & """,@)),row(@)-min(row(@))+1,false))", "@", .Columns(1).Address)), False, False)
         If UBound(Rws) < 0 Then
            FoundSomething = True
         ElseIf UBound(Rws) = 0 Then
            myList = .Parent.Range("A" & Rws(0) + .Row - 1).Resize(, 14).Value
         Else
            myList = Application.Index(.Value, Application.Transpose(Rws), [sequence(,14)])
         End If
      End With
   End With
    If Not FoundSomething Then
    ListBox.RowSource = ""
        ListBox.ColumnCount = 14
        ListBox.ColumnHeads = True
        ListBox.ColumnWidths = "55,220,100,100,70,70,70,55,130,80,80,70,150,150"
        ListBox.List = myList
    Else
        Call Refresh_data
    End If

End Sub
 
Last edited:
Upvote 0
Solution
Hi Fluff, thanks for your help! I try to implement your code but face some problems...

The screenshot for my userform:
1653498954592.png


Changed your code based on my naming:
VBA Code:
 Private Sub txtSearch_Change()

    Dim myList As Variant, Rws As Variant
    Dim FoundSomething As Boolean
    With Sheets("Sheet1")
      With .Range("A6:N" & .Range("A" & Rows.Count).End(xlUp).Row)
         Rws = Filter(.Worksheet.Evaluate(Replace("transpose(if(isnumber(search(""" & Me.txtSearch.Value & """,@)),row(@)-min(row(@))+1,false))", "@", .Columns(1).Address)), False, False)
         If UBound(Rws) < 0 Then
            FoundSomething = True
         ElseIf UBound(Rws) = 0 Then
            myList = .Parent.Range("A" & Rws(0)).Resize(, 14).Value
         Else
            myList = Application.Index(.Value, Application.Transpose(Rws), [sequence(,14)])
         End If
      End With
   End With
    If Not FoundSomething Then
    ListBox.RowSource = ""
        ListBox.ColumnCount = 14
        ListBox.ColumnHeads = True
        ListBox.ColumnWidths = "55,220,100,100,70,70,70,55,130,80,80,70,150,150"
        ListBox.List = myList
    Else
        Call Refresh_data
    End If

End Sub

I input some data and try to run your code for filtering, and it seems like the filter didn't do its work...
1653499076101.png
 
Upvote 0
Forgot to allow for your data starting in row 6, if there is only one match, try
VBA Code:
         ElseIf UBound(Rws) = 0 Then
            myList = .Parent.Range("A" & Rws(0) + .Row - 1).Resize(, 14).Value
         Else
 
Upvote 0
Now the filter works but new problem occur. My previous code included listbox_double click activity, meaning after I click the filtered data, my other textboxes will track the specific filtered data:
VBA Code:
Private Sub ListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    
        txtSearch.Text = Me.ListBox.Column(0)
        txtSearch1.Text = Me.ListBox.Column(2)
        txtSAPCode.Text = Me.ListBox.Column(0)
        txtSAPDescription.Text = Me.ListBox.Column(1)
        txtBookingNo.Text = Me.ListBox.Column(2)
        txtShipmentNo.Text = Me.ListBox.Column(3)
        txtPONo.Text = Me.ListBox.Column(4)
        txtDateFrom.Text = Me.ListBox.Column(5)
        txtDateTo.Text = Me.ListBox.Column(6)
        txtQuantity.Text = Me.ListBox.Column(7)
        txtManufacturingDate.Text = Me.ListBox.Column(8)
        txtCapsuleBatch1.Text = Me.ListBox.Column(9)
        txtCapsuleBatch2.Text = Me.ListBox.Column(10)
        txtDateofDespatch.Text = Me.ListBox.Column(11)
        txtTrackSubmittedBy.Text = Me.ListBox.Column(12)
        txtTrackSubmittedOn.Text = Me.ListBox.Column(13)
    
End Sub

But now after I double click the specific filtered data, error occur:
1653500200433.png


And seems like code cannot capture the column heads:
1653500311184.png


Really appreciate your help!!
 
Upvote 0
That's because you are using the textbox change event & that code is changing the textbox.
However as this is a totally different question, it needs a new thread. Thanks
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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