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

 

[Norie]

Do you have the RowSource property of the listbox set?

 

[adm1neca]

No, but i am adding items with the AddItem. If the cells are in the same sheet it works perfectly fine, however if i want to take them from another i get this problem.

 

[Norie]

Where exactly do you get the error?

Also, which worksheet is causing the problem?

Is it the worksheet that Sheets(8) refers to if the worksheet with the tab name 'array_db'?

 

[adm1neca]

The error is here right after the IF:

Me.ListBox1.AddItem ActiveWorkbook.Sheets(8).Cells(x, 1)

Yes Sheets(8) is the same as array_db; just wanted to check if that was an issue

 

[Norie]

What happens if you use Sheets("array_db") instead of Sheets(8)?

 

[adm1neca]

What happens if you use Sheets("array_db") instead of Sheets(8)?

Same error. Is there another way to show values in listbox based on criteria ?

 

[Norie]

What happens if you add this to the code?

Me.ListBox1.RowSource = ""

 

[adm1neca]

I recieve error 438 - objecct does not support this property or method.

 

[Norie]

Where is this listbox located?

I've been assuming it was on a userform.