Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2008

Tip: Looking for answers? Try searching our database.

Problem Setting Worksheet

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Texas Aggie - 20 May 2008 18:10 GMT
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

Signature

Fighting Texas Aggie Class of 2009

Susan - 20 May 2008 18:37 GMT
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

> Here is my code:
>
[quoted text clipped - 39 lines]
>
> Fighting Texas Aggie Class of 2009
Texas Aggie - 20 May 2008 19:21 GMT
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.
Signature

Fighting Texas Aggie Class of 2009

> i believe that when you start specifying sheets, you also have to
> specify the workbook..................
[quoted text clipped - 57 lines]
> >
> > Fighting Texas Aggie Class of 2009
Susan - 20 May 2008 19:34 GMT
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

> 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
[quoted text clipped - 65 lines]
>
> - Show quoted text -
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.