Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with setting the worksheet variable ws | Excel Discussion (Misc queries) | |||
problem setting formula | Excel Worksheet Functions | |||
setting FaceID problem | Excel Programming | |||
Problem setting up formulas | New Users to Excel | |||
Problem with setting the ListFillRange in VBA | Excel Programming |