fixing error permission denied



I  need  fixing  this  error"permission denied" in this line

 Me.ListBox1.List = a
  I  modified  something  in  theses codes  every thing  is  ok   except  one  thing   if  I  select  the  sheet  from combobox  and  write  the  item in textbox based on COL D  .  it  shows  the  error. my  orginal  code  populate  data in listbox  based on first sheet and  write  the  item in textbox1  . but  now  I want making  code more flexable  by  add  another choices by  using combobox  and  select  the sheet and  serch in textbox  the  item then populate data in listbox .

any  help would  appreciate




I think it's a mistake to call a Variant variable just "a" (better to use Arr or something more descriptive IMHO) but what are you trying to do with the line? Restore the listbox from a saved array?
John_Ru (rep: 2467) Sep 25, '21 at 1:00 pm
Hi John,
it should do that as in first case when run the userform call restore array when select specific sheet.
Mussa (rep: 18) Sep 27, '21 at 4:33 am
Hi Mussa. I'm still not clear what you're trying to do with that variable so can't answer.
John_Ru (rep: 2467) Sep 27, '21 at 12:20 pm
recently restore the listbox from saved array (the first sheet) but it should expand array by making based on selected the sheet from combobox
Mussa (rep: 18) Sep 28, '21 at 11:20 am
Add to Discussion


Selected Answer


I don't think you need the line you added (Me.ListBox1.List = a) but you need to dissociate Listbox1 from its RowSource before your clear it (then filter).

I've renamed your Public variable "a" to Arr (for easier reading) then made these changes:

1) Modified the UserForm1 to add labels (espeically "Fieldname" since my guess if that you will change the ID-CC label to a ComboBox (to allow other fields to be searched)  

2) Changed the background macros as follows (see lines in bold/ comments):

Private Sub ComboBox1_Change()
  With ComboBox1
    If .Value = "" Then
      'Me.ListBox1.List = Arr
      Exit Sub
    End If

    If .ListIndex = -1 Then Exit Sub
    ListBox1.RowSource = ""
    ListBox1.RowSource = "'" & Sheets(.Value).Name & "'!" & Sheets(.Value).Range("A1", Sheets(.Value).Range("H" & Rows.Count).End(3)).Address
    TextBox1 = "" 'added- clear search box
  End With
End Sub
so when you change sheets, there's a full list and no misleading filter text.


Sub FilterData()
    Dim i As Long, ii As Long, n As Long
    'Me.ListBox1.List = Arr
    If Me.TextBox1 = "" Then Exit Sub
    With Me.ListBox1
        If .ListCount > 0 Then ' ## added
            .RowSource = "" ' ## added
        End If
        For i = 0 To UBound(Arr, 1)
            If UCase$(Arr(i, 3)) Like "*" & UCase$(Me.TextBox1) & "*" Then ' ## changed
                .List(n, 0) = n + 1
                For ii = 1 To UBound(Arr, 2)
                    .List(n, ii) = Arr(i, ii)
                n = n + 1
            End If
    End With
End Sub
where the If . ListBox1... statement checks if there's a list, clears the row source to allow the clear (and the subsequent re-building of Listbox1 with filterring). The line starting If UCase$(Arr(i, 3)) adds wildcards around the TextBox1 search value so that you can now type "01" (say) and only the entries containing that string will be displayed. I hope this is better/ faster for your users.

In anticipation of you using a "Fieldname" ComboBox to search other fields, I've extneded the listbox contents (on change of Sheet source) to include the header row to remind users of what text string they might be searching in.

Hope this works for you.



first my apologies  from the beginning is not clear for you 
second this is perfect, and thank you so much .
Mussa (rep: 18) Sep 30, '21 at 7:43 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login