Run-Time error '9' New Question
 

I have posted this problem before and didnt get it resolved, fishing again
hoping to get lucky.

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. Also with the help of a few members here I
have also ruled out adding the code setting the workbook. Any help you can
give me would be most appreciated.

Thanks in advance,
Ryan

--

Fighting Texas Aggie Class of 2009

Run-Time error '9' New Question
 

I think the problem could be this line:

Set myRange = Range("F2", rngNext)

You could try:

Set myRange = Range(ws.range("F2"), rngNext)


"Texas Aggie" wrote:

I have posted this problem before and didnt get it resolved, fishing again
hoping to get lucky.

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. Also with the help of a few members here I
have also ruled out adding the code setting the workbook. Any help you can
give me would be most appreciated.

Thanks in advance,
Ryan

--

Fighting Texas Aggie Class of 2009

Run-Time error '9' New Question
 

You don't have a worksheet named List1 in the activeworkbook.

Maybe the workbook you want to use isn't active. Or maybe there's a typo in the
code or in the name of the worksheet.

And after you find your solution to that, I'd drop the .select's and use
something like:



Dim wsSheet As Worksheet
Dim rngNext As Range
Dim myRange As Range

Set wsSheet = thisworkbook.worksheets("List1") 'ActiveSheet

With wsSheet
Set myRange = .Range("F2", .cells(.rows.count,"F").end(xlup))
End With

'populate Group combobox list
With combStation
For Each rngNext In myRange.Cells
If rngNext.value = "" Then
'do nothing
else
.AddItem rngNext.Value
end if
Next rngNext
End With

Texas Aggie wrote:

I have posted this problem before and didnt get it resolved, fishing again
hoping to get lucky.

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. Also with the help of a few members here I
have also ruled out adding the code setting the workbook. Any help you can
give me would be most appreciated.

Thanks in advance,
Ryan

--

Fighting Texas Aggie Class of 2009

--

Dave Peterson

Run-Time error '9' New Question
 

Your awsome,

the new code works and dropping the .selects was the key. Thanks alot for
the help this has been hanging over my head since last night.
--

Fighting Texas Aggie Class of 2009


"Dave Peterson" wrote:

You don't have a worksheet named List1 in the activeworkbook.

Maybe the workbook you want to use isn't active. Or maybe there's a typo in the
code or in the name of the worksheet.

And after you find your solution to that, I'd drop the .select's and use
something like:



Dim wsSheet As Worksheet
Dim rngNext As Range
Dim myRange As Range

Set wsSheet = thisworkbook.worksheets("List1") 'ActiveSheet

With wsSheet
Set myRange = .Range("F2", .cells(.rows.count,"F").end(xlup))
End With

'populate Group combobox list
With combStation
For Each rngNext In myRange.Cells
If rngNext.value = "" Then
'do nothing
else
.AddItem rngNext.Value
end if
Next rngNext
End With

Texas Aggie wrote:

I have posted this problem before and didnt get it resolved, fishing again
hoping to get lucky.

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. Also with the help of a few members here I
have also ruled out adding the code setting the workbook. Any help you can
give me would be most appreciated.

Thanks in advance,
Ryan

--

Fighting Texas Aggie Class of 2009

--

Dave Peterson