help populate data in listbox based on combobox

[abdelfattah]

hello
i have sheet contains data from a3 : g1000 and userform contains combobox1 and listbox
the proplem when i run userform i can't access to it an show me error run time error70 permission is denied
i hope any body help

Option Explicit
Dim myData As Range

Private Sub ComboBox1_Change()

  Me.ListBox1.ListIndex = Me.ComboBox1.ListIndex
Dim mySearchRng As Range
Dim myFindRng As Range
Dim myValToFind As String

With Worksheets("sheet3")
myValToFind = ComboBox1.Value
Set mySearchRng = .Columns("g")
End With

Set myFindRng = mySearchRng.Find(What:=myValToFind, _
        LookIn:=xlFormulas, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)

    ListBox1.AddItem
    With ListBox1
        .List(.ListCount - 1, 0) = myFindRng.Value  'Data from Col A
        .List(.ListCount - 1, 1) = myFindRng.Offset(0, 2).Value 'Data from Col B
        .List(.ListCount - 1, 2) = myFindRng.Offset(0, 3).Value 'Data from Col C
        .List(.ListCount - 1, 3) = myFindRng.Offset(0, 4).Value 'Data from Col D
        .List(.ListCount - 1, 4) = myFindRng.Offset(0, 5).Value 'Data from Col E
        .List(.ListCount - 1, 5) = myFindRng.Offset(0, 6).Value 'Data from Col f
        .List(.ListCount - 1, 6) = myFindRng.Offset(0, 7).Value 'Data from Col g
        
    End With
    
End Sub


Private Sub UserForm_Initialize()
Set myData = Sheet3.Range("g3").CurrentRegion
Me.ComboBox1.List = myData.Offset(7).Value
Me.ListBox1.ColumnCount = 7
Me.ListBox1.List = Me.ComboBox1.List
End Sub

 

[DanteAmor]

The error is because you first loaded the listbox with the .List property

Me.ListBox1.List = Me.ComboBox1.List

and then you are trying to add records to the listbox with the .AddItem method.
ListBox1.AddItem

Explain what your goal is.

 

[abdelfattah]

it doesn't work what i would when i choose the data from combobox it shows the data in list box

 

[DanteAmor]

I did not put a solution, I just said what the problem is. To give a solution you must say that you need.

 

[abdelfattah]

i ever have told you what i would more again i have sheet contains data from a3 : g1000 and userform contains combobox1 and listbox 1 when i choose the data from combobox it shows the result in listbox you can see the image for instance when i choose the name from combobox it only shows data of john or adam ... etc i hope this help

 

[DanteAmor]

You can put a sample of your data. if you select john what do you want in the listbox

 

[abdelfattah]

show data of john from a3:f1000 on listbox the data in sheet as the image

 

[DanteAmor]

Delete this line and try
Me.ListBox1.List = Me.ComboBox1.List

 

[abdelfattah]

it still the problem continues

 

[DanteAmor]

Try this code

Option Explicit
Dim sh As Worksheet

Private Sub ComboBox1_Change()
  Dim f As Range, r As Range, cell As String
  
  ListBox1.Clear
  If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
  
  Set r = sh.Range("G4:G" & sh.Range("G" & Rows.Count).End(xlUp).Row)
  Set f = r.Find(ComboBox1.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      With ListBox1
        .AddItem sh.Range("A" & f.Row).Value
        .List(.ListCount - 1, 1) = sh.Range("B" & f.Row).Value 'Data from Col B
        .List(.ListCount - 1, 2) = sh.Range("C" & f.Row).Value 'Data from Col C
        .List(.ListCount - 1, 3) = sh.Range("D" & f.Row).Value 'Data from Col D
        .List(.ListCount - 1, 4) = sh.Range("E" & f.Row).Value 'Data from Col E
        .List(.ListCount - 1, 5) = sh.Range("F" & f.Row).Value 'Data from Col f
        .List(.ListCount - 1, 6) = sh.Range("G" & f.Row).Value 'Data from Col g
      End With
      Set f = r.FindNext(f)
    Loop While Not f Is Nothing And f.Address <> cell
  End If
End Sub

Private Sub UserForm_Initialize()
  Dim i As Long, dic As Object
  
  Set sh = Sheet3
  Set dic = CreateObject("Scripting.Dictionary")
  
  For i = 4 To sh.Range("G" & Rows.Count).End(xlUp).Row
    dic(sh.Range("G" & i).Value) = Empty
  Next
  ComboBox1.List = dic.keys
  ListBox1.ColumnCount = 7
End Sub

___________________________________________________________________
I attach my file for you to try

Dropbox - File Deleted - Simplify your life

www.dropbox.com

___________________________________________________________________