Permission denied when trying to get cell values from another sheet

[adm1neca]

Dear All,

I turn to you, because i ran into some troubles. I want to get the value from certain cell and put it into listbox.

Here is the code i am using
I get permission denied when the for cycle starts. If i have the values of the cells in the same sheet everything works great.

Private Sub CommandButton1_Click()
crit = Val(ComboBox2.Value)

If ActiveWorkbook.Worksheets("array_db").Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
lr = 2
Else
lr = ActiveWorkbook.Worksheets("array_db").Cells(Rows.Count, 1).End(xlUp).Row
End If
'clear thebox

For x = 2 To lr


If ActiveWorkbook.Sheets("array_db").Cells(x, 1) = crit Then
'write it to the listbox
Me.ListBox1.AddItem ActiveWorkbook.Sheets(8).Cells(x, 1)


Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = ActiveWorkbook.Worksheets("array_db").Cells(x, 2)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = ActiveWorkbook.Worksheets("array_db").Cells(x, 3)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = ActiveWorkbook.Worksheets("array_db").Cells(x, 4)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = ActiveWorkbook.Worksheets("array_db").Cells(x, 5)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = ActiveWorkbook.Worksheets("array_db").Cells(x, 6)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 6) = ActiveWorkbook.Worksheets("array_db").Cells(x, 7)
End If
 
Next x


End Sub

 

[adm1neca]

I was not... My bad... This is the code i am using and it works as a charm for now:

Private Sub CommandButton1_Click()
ListBox1.Clear
crit = Val(ComboBox1.Value)






If ActiveWorkbook.Worksheets("array_db").Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
lr = 2
Else
lr = ActiveWorkbook.Worksheets("array_db").Cells(Rows.Count, 1).End(xlUp).Row
End If






For x = 2 To lr


If ActiveWorkbook.Worksheets("array_db").Cells(x, 1) = crit Then
'write it to the listbox
Me.ListBox1.AddItem ActiveWorkbook.Worksheets("array_db").Cells(x, 1)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = ActiveWorkbook.Worksheets("array_db").Cells(x, 2)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = ActiveWorkbook.Worksheets("array_db").Cells(x, 3)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = ActiveWorkbook.Worksheets("array_db").Cells(x, 4)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = ActiveWorkbook.Worksheets("array_db").Cells(x, 5)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = ActiveWorkbook.Worksheets("array_db").Cells(x, 6)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 6) = ActiveWorkbook.Worksheets("array_db").Cells(x, 7)
End If
 
Next x


End Sub

Can anyone help me with my next question - I want to order the data it shows in the listbox by the 6 column in ascending order?

 

[Norie]

Sorry I'm confused, where is the listbox located?

Is the problem now solved?

 

[adm1neca]

It is on userform. The data is taken from sheet - array_db

 

[Norie]

So the problem is solved?

 

[adm1neca]

yes