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

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" wrote:

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

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

"Joel" wrote:


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" wrote:

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

which statement fails???

"Mekinnik" wrote:

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

"Joel" wrote:


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" wrote:

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

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

"Joel" wrote:

which statement fails???

"Mekinnik" wrote:

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

"Joel" wrote:


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" wrote:

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

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.

"Mekinnik" wrote:

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

"Joel" wrote:

which statement fails???

"Mekinnik" wrote:

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

"Joel" wrote:


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" wrote:

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