Problem Setting Worksheet
 

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

--

Fighting Texas Aggie Class of 2009

Problem Setting Worksheet
 

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



On May 20, 1:10*pm, Texas Aggie
wrote:
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

--

Fighting Texas Aggie Class of 2009

Problem Setting Worksheet
 

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


"Susan" wrote:

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



On May 20, 1:10 pm, Texas Aggie
wrote:
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

--

Fighting Texas Aggie Class of 2009

Problem Setting Worksheet
 

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



On May 20, 2:21*pm, Texas Aggie
wrote:
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



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

On May 20, 1:10 pm, Texas Aggie
wrote:
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

--

Fighting Texas Aggie Class of 2009- Hide quoted text -

- Show quoted text -