Help with code repair

[Mekinnik]

How can I modify the code below to reference a non-active worksheet, I do not
want to use 'Activeworksheet'

Dim dX As Double, dCount As Double
Worksheets("test").Activate
sSelected = Me.Cbo1.Value
dCount = 0
If IsError(Application.Match(sSelected, ActiveSheet.Columns(1), 0)) Then
frm1.Hide
frm2.Show
End If
For dX = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(dX, 1).Value = sSelected Then
Me.Cbo2.AddItem Cells(dX, 2).Value
dCount = dCount + 1
End If
Next
End Sub

 

[Joel]

Dim dX As Double, dCount As Double
with Worksheets("test")
sSelected = Me.Cbo1.Value
dCount = 0
If IsError(Application.Match(sSelected, .Columns(1), 0)) Then
frm1.Hide
frm2.Show
End If
For dX = 1 To .UsedRange.Rows.Count
If .Cells(dX, 1).Value = sSelected Then
Me.Cbo2.AddItem .Cells(dX, 2).Value
dCount = dCount + 1
End If
Next
end with
End Sub

 

[Mekinnik]

Sorry but it doesn't work, for some odd reason it will only work with
'activesheet'

 

[Joel]

which statement fails???

Sorry but it doesn't work, for some odd reason it will only work with
'activesheet'

 

[Mekinnik]

This one
For dX = 1 To .UsedRange.Rows.Count

which statement fails???

 

[Joel]

I'm not seeing any problems, but used range varies with different type date.
I think you need to check if usedrange is returning the same vaule ifthe page
is selected or not selected. If you single step through the code and one
time have Test selected and another time have a different sheet slected do
you get the same results? Your problem may not be with the activesheet.