Problem Setting Worksheet

[Texas Aggie]

Here is my code:

Dim wsSheet As Worksheet
Dim rngNext As Range
Dim myRange As Range
Set wsSheet = ActiveSheet
With wsSheet
Set rngNext = .Range("F65536").End(xlUp).Offset(1, 0)
End With
rngNext.Select
Set myRange = Range("F2", rngNext)

'populate Group combobox list
With combStation
Selection.End(xlUp).Select
For Each rngNext In myRange
If rngNext = "" Then
Selection.End(xlUp).Select
End If
.AddItem rngNext
Next rngNext
End With

Here is my problem:

When I have the worksheet set at:

Set wsSheet = ActiveSheet

The userform works as planned and I continue. When I try to set it to:

Set wsSheet = Worksheets("List1")

I get a Run-Time error '9': and the userform fails. I have isolated the
problem to that line a code. I tried the obvious and saved the workbook.
From there I need ya'll help.

Thanks in advance,
Ryan

 

[Susan]

i believe that when you start specifying sheets, you also have to
specify the workbook..................
near the beginning if you were to:

dim wb as workbook

set wb = activeworkbook

then try

Set wsSheet = wb.Worksheets("List1")

"worksheets" is a collection of objects that have to belong to
something...... hence the wb.
i could be wrong, but that's my take on it.
hope it helps
susan

 

[Texas Aggie]

I see where you were going with that but I dont believe you have to state the
workbook in the code as it is usually understood when working with only one
workbook. I went ahead an tried your advice and the problem continues.
--
Fighting Texas Aggie Class of 2009

i believe that when you start specifying sheets, you also have to
specify the workbook..................
near the beginning if you were to:

dim wb as workbook

set wb = activeworkbook

then try

Set wsSheet = wb.Worksheets("List1")

"worksheets" is a collection of objects that have to belong to
something...... hence the wb.
i could be wrong, but that's my take on it.
hope it helps
susan

 

[Susan]

ok.
all i can come up with are other award-winning suggestions like "are
you sure you have a worksheet by that name in the workbook?", which
you probably do.
i looked at some other posts on run-time error #9 & couldn't come up
with anything else that might be causing it for you.
might be something basic like "worksheet" instead of "worksheets".
sorry can't seem to help you out.
susan