Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
R/T Error - Why?
Trying to get sys working, but below doesn't work (No Userforms here, only WS)
Private Sub Worksheet_Activate() With ActiveSheet.ComboBox1 ' A Control toolbox type CB .ListFillRange = Range("Sheetlist") 'SheetList is dynamic range via =Offset(X,0,0,Counta(A:A),1) .ListIndex = 0 End With End Sub Also tried .Rowsource inplace of ListFillRange, buy NO CIGAR, Why is that? TIA,,, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
R/T Error - Why?
ListfillRange want a string like Sheet1!A1:B10. You can get the range by
using the method such as ..ListFillRange = Range("Sheetlist").address This doesn't always work because address doesn't return the sheet name. Instead you can use this ..ListFillRange = Mid(Names("Sheetlist").Value, 2) The Names method return an equal sign at the beginning so you need to remove the equal sign with the mid function. "JMay" wrote: Trying to get sys working, but below doesn't work (No Userforms here, only WS) Private Sub Worksheet_Activate() With ActiveSheet.ComboBox1 ' A Control toolbox type CB .ListFillRange = Range("Sheetlist") 'SheetList is dynamic range via =Offset(X,0,0,Counta(A:A),1) .ListIndex = 0 End With End Sub Also tried .Rowsource inplace of ListFillRange, buy NO CIGAR, Why is that? TIA,,, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
R/T Error - Why?
Joel,
Thanks.. the ..ListFillRange = Range("Sheetlist").address << works fine, but I pickup on what you are saying about it maybe missing the sheetName, if required. I also tried the ..ListFillRange = Mid(Names("Sheetlist").Value, 2) but am getting a R/T error 1004 Application-defined or Object-defined error.. Thanks for your help "Joel" wrote: ListfillRange want a string like Sheet1!A1:B10. You can get the range by using the method such as .ListFillRange = Range("Sheetlist").address This doesn't always work because address doesn't return the sheet name. Instead you can use this .ListFillRange = Mid(Names("Sheetlist").Value, 2) The Names method return an equal sign at the beginning so you need to remove the equal sign with the mid function. "JMay" wrote: Trying to get sys working, but below doesn't work (No Userforms here, only WS) Private Sub Worksheet_Activate() With ActiveSheet.ComboBox1 ' A Control toolbox type CB .ListFillRange = Range("Sheetlist") 'SheetList is dynamic range via =Offset(X,0,0,Counta(A:A),1) .ListIndex = 0 End With End Sub Also tried .Rowsource inplace of ListFillRange, buy NO CIGAR, Why is that? TIA,,, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
R/T Error - Why?
See what is being returned
msgbox(Mid(Names("Sheetlist").Value, 2)) Error 1004 is usually because the Sheet Name isn't found. "JMay" wrote: Joel, Thanks.. the .ListFillRange = Range("Sheetlist").address << works fine, but I pickup on what you are saying about it maybe missing the sheetName, if required. I also tried the .ListFillRange = Mid(Names("Sheetlist").Value, 2) but am getting a R/T error 1004 Application-defined or Object-defined error.. Thanks for your help "Joel" wrote: ListfillRange want a string like Sheet1!A1:B10. You can get the range by using the method such as .ListFillRange = Range("Sheetlist").address This doesn't always work because address doesn't return the sheet name. Instead you can use this .ListFillRange = Mid(Names("Sheetlist").Value, 2) The Names method return an equal sign at the beginning so you need to remove the equal sign with the mid function. "JMay" wrote: Trying to get sys working, but below doesn't work (No Userforms here, only WS) Private Sub Worksheet_Activate() With ActiveSheet.ComboBox1 ' A Control toolbox type CB .ListFillRange = Range("Sheetlist") 'SheetList is dynamic range via =Offset(X,0,0,Counta(A:A),1) .ListIndex = 0 End With End Sub Also tried .Rowsource inplace of ListFillRange, buy NO CIGAR, Why is that? TIA,,, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
R/T Error - Why?
Still getting 1004;
My SheetList range name in it's RefersTo(Box) contains: =OFFSET(SetUp!$A$1,,,COUNTA(SetUp!$A:$A),1) My Code to date is: Private Sub Worksheet_Activate() With ActiveSheet.ComboBox1 ' A Control toolbox type CB ' .ListFillRange = Mid(Names("SheetList").Value, 2) MsgBox (Mid(Names("SetUp!Sheetlist").Value, 2)) ' .ListFillRange = Range("Sheetlist").Address 'SheetList is dynamic range ' .ListIndex = 0 End With End Sub Thanks for your help. "Joel" wrote: See what is being returned msgbox(Mid(Names("Sheetlist").Value, 2)) Error 1004 is usually because the Sheet Name isn't found. "JMay" wrote: Joel, Thanks.. the .ListFillRange = Range("Sheetlist").address << works fine, but I pickup on what you are saying about it maybe missing the sheetName, if required. I also tried the .ListFillRange = Mid(Names("Sheetlist").Value, 2) but am getting a R/T error 1004 Application-defined or Object-defined error.. Thanks for your help "Joel" wrote: ListfillRange want a string like Sheet1!A1:B10. You can get the range by using the method such as .ListFillRange = Range("Sheetlist").address This doesn't always work because address doesn't return the sheet name. Instead you can use this .ListFillRange = Mid(Names("Sheetlist").Value, 2) The Names method return an equal sign at the beginning so you need to remove the equal sign with the mid function. "JMay" wrote: Trying to get sys working, but below doesn't work (No Userforms here, only WS) Private Sub Worksheet_Activate() With ActiveSheet.ComboBox1 ' A Control toolbox type CB .ListFillRange = Range("Sheetlist") 'SheetList is dynamic range via =Offset(X,0,0,Counta(A:A),1) .ListIndex = 0 End With End Sub Also tried .Rowsource inplace of ListFillRange, buy NO CIGAR, Why is that? TIA,,, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
R/T Error - Why?
Names is a workbook object that doesn't need a sheet reference. The value
that is returned does require a sheet reference. You can see the names in tweo places 1) Insert - Name - Define 2) File - Properties - Custom from MsgBox (Mid(Names("SetUp!Sheetlist").Value, 2)) to MsgBox (Mid(Names("Sheetlist").Value, 2)) "JMay" wrote: Still getting 1004; My SheetList range name in it's RefersTo(Box) contains: =OFFSET(SetUp!$A$1,,,COUNTA(SetUp!$A:$A),1) My Code to date is: Private Sub Worksheet_Activate() With ActiveSheet.ComboBox1 ' A Control toolbox type CB ' .ListFillRange = Mid(Names("SheetList").Value, 2) MsgBox (Mid(Names("SetUp!Sheetlist").Value, 2)) ' .ListFillRange = Range("Sheetlist").Address 'SheetList is dynamic range ' .ListIndex = 0 End With End Sub Thanks for your help. "Joel" wrote: See what is being returned msgbox(Mid(Names("Sheetlist").Value, 2)) Error 1004 is usually because the Sheet Name isn't found. "JMay" wrote: Joel, Thanks.. the .ListFillRange = Range("Sheetlist").address << works fine, but I pickup on what you are saying about it maybe missing the sheetName, if required. I also tried the .ListFillRange = Mid(Names("Sheetlist").Value, 2) but am getting a R/T error 1004 Application-defined or Object-defined error.. Thanks for your help "Joel" wrote: ListfillRange want a string like Sheet1!A1:B10. You can get the range by using the method such as .ListFillRange = Range("Sheetlist").address This doesn't always work because address doesn't return the sheet name. Instead you can use this .ListFillRange = Mid(Names("Sheetlist").Value, 2) The Names method return an equal sign at the beginning so you need to remove the equal sign with the mid function. "JMay" wrote: Trying to get sys working, but below doesn't work (No Userforms here, only WS) Private Sub Worksheet_Activate() With ActiveSheet.ComboBox1 ' A Control toolbox type CB .ListFillRange = Range("Sheetlist") 'SheetList is dynamic range via =Offset(X,0,0,Counta(A:A),1) .ListIndex = 0 End With End Sub Also tried .Rowsource inplace of ListFillRange, buy NO CIGAR, Why is that? TIA,,, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
R/T Error - Why?
Well, Joel --- I GIVE UP !!
I've been on this for 3+ hours and I'm getting very strung out,,,,grrrrrr The revised line MsgBox (Mid(Names("Sheetlist").Value, 2)) still yeilds the 1004 error Thanks for your time, and concern. Jim "Joel" wrote: Names is a workbook object that doesn't need a sheet reference. The value that is returned does require a sheet reference. You can see the names in tweo places 1) Insert - Name - Define 2) File - Properties - Custom from MsgBox (Mid(Names("SetUp!Sheetlist").Value, 2)) to MsgBox (Mid(Names("Sheetlist").Value, 2)) "JMay" wrote: Still getting 1004; My SheetList range name in it's RefersTo(Box) contains: =OFFSET(SetUp!$A$1,,,COUNTA(SetUp!$A:$A),1) My Code to date is: Private Sub Worksheet_Activate() With ActiveSheet.ComboBox1 ' A Control toolbox type CB ' .ListFillRange = Mid(Names("SheetList").Value, 2) MsgBox (Mid(Names("SetUp!Sheetlist").Value, 2)) ' .ListFillRange = Range("Sheetlist").Address 'SheetList is dynamic range ' .ListIndex = 0 End With End Sub Thanks for your help. "Joel" wrote: See what is being returned msgbox(Mid(Names("Sheetlist").Value, 2)) Error 1004 is usually because the Sheet Name isn't found. "JMay" wrote: Joel, Thanks.. the .ListFillRange = Range("Sheetlist").address << works fine, but I pickup on what you are saying about it maybe missing the sheetName, if required. I also tried the .ListFillRange = Mid(Names("Sheetlist").Value, 2) but am getting a R/T error 1004 Application-defined or Object-defined error.. Thanks for your help "Joel" wrote: ListfillRange want a string like Sheet1!A1:B10. You can get the range by using the method such as .ListFillRange = Range("Sheetlist").address This doesn't always work because address doesn't return the sheet name. Instead you can use this .ListFillRange = Mid(Names("Sheetlist").Value, 2) The Names method return an equal sign at the beginning so you need to remove the equal sign with the mid function. "JMay" wrote: Trying to get sys working, but below doesn't work (No Userforms here, only WS) Private Sub Worksheet_Activate() With ActiveSheet.ComboBox1 ' A Control toolbox type CB .ListFillRange = Range("Sheetlist") 'SheetList is dynamic range via =Offset(X,0,0,Counta(A:A),1) .ListIndex = 0 End With End Sub Also tried .Rowsource inplace of ListFillRange, buy NO CIGAR, Why is that? TIA,,, |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
R/T Error - Why?
I'd use:
..ListFillRange = Range("Sheetlist").address(external:=true) JMay wrote: Joel, Thanks.. the .ListFillRange = Range("Sheetlist").address << works fine, but I pickup on what you are saying about it maybe missing the sheetName, if required. I also tried the .ListFillRange = Mid(Names("Sheetlist").Value, 2) but am getting a R/T error 1004 Application-defined or Object-defined error.. Thanks for your help "Joel" wrote: ListfillRange want a string like Sheet1!A1:B10. You can get the range by using the method such as .ListFillRange = Range("Sheetlist").address This doesn't always work because address doesn't return the sheet name. Instead you can use this .ListFillRange = Mid(Names("Sheetlist").Value, 2) The Names method return an equal sign at the beginning so you need to remove the equal sign with the mid function. "JMay" wrote: Trying to get sys working, but below doesn't work (No Userforms here, only WS) Private Sub Worksheet_Activate() With ActiveSheet.ComboBox1 ' A Control toolbox type CB .ListFillRange = Range("Sheetlist") 'SheetList is dynamic range via =Offset(X,0,0,Counta(A:A),1) .ListIndex = 0 End With End Sub Also tried .Rowsource inplace of ListFillRange, buy NO CIGAR, Why is that? TIA,,, -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
R/T Error - Why?
Ther are two reasons you have the problem
1) You have more than one workbook opened and it is looking for the named range in the wrong workbook. Try MsgBox (Mid(workbooks("book1.xls").Names("Sheetlist").Val ue, 2)) 2) You are spelling the Name Range incorrectly. Check the worksheet menu Insert - Name - Define Make sure you are spelling the named range exactly like it is spelled in the menu. "JMay" wrote: Well, Joel --- I GIVE UP !! I've been on this for 3+ hours and I'm getting very strung out,,,,grrrrrr The revised line MsgBox (Mid(Names("Sheetlist").Value, 2)) still yeilds the 1004 error Thanks for your time, and concern. Jim "Joel" wrote: Names is a workbook object that doesn't need a sheet reference. The value that is returned does require a sheet reference. You can see the names in tweo places 1) Insert - Name - Define 2) File - Properties - Custom from MsgBox (Mid(Names("SetUp!Sheetlist").Value, 2)) to MsgBox (Mid(Names("Sheetlist").Value, 2)) "JMay" wrote: Still getting 1004; My SheetList range name in it's RefersTo(Box) contains: =OFFSET(SetUp!$A$1,,,COUNTA(SetUp!$A:$A),1) My Code to date is: Private Sub Worksheet_Activate() With ActiveSheet.ComboBox1 ' A Control toolbox type CB ' .ListFillRange = Mid(Names("SheetList").Value, 2) MsgBox (Mid(Names("SetUp!Sheetlist").Value, 2)) ' .ListFillRange = Range("Sheetlist").Address 'SheetList is dynamic range ' .ListIndex = 0 End With End Sub Thanks for your help. "Joel" wrote: See what is being returned msgbox(Mid(Names("Sheetlist").Value, 2)) Error 1004 is usually because the Sheet Name isn't found. "JMay" wrote: Joel, Thanks.. the .ListFillRange = Range("Sheetlist").address << works fine, but I pickup on what you are saying about it maybe missing the sheetName, if required. I also tried the .ListFillRange = Mid(Names("Sheetlist").Value, 2) but am getting a R/T error 1004 Application-defined or Object-defined error.. Thanks for your help "Joel" wrote: ListfillRange want a string like Sheet1!A1:B10. You can get the range by using the method such as .ListFillRange = Range("Sheetlist").address This doesn't always work because address doesn't return the sheet name. Instead you can use this .ListFillRange = Mid(Names("Sheetlist").Value, 2) The Names method return an equal sign at the beginning so you need to remove the equal sign with the mid function. "JMay" wrote: Trying to get sys working, but below doesn't work (No Userforms here, only WS) Private Sub Worksheet_Activate() With ActiveSheet.ComboBox1 ' A Control toolbox type CB .ListFillRange = Range("Sheetlist") 'SheetList is dynamic range via =Offset(X,0,0,Counta(A:A),1) .ListIndex = 0 End With End Sub Also tried .Rowsource inplace of ListFillRange, buy NO CIGAR, Why is that? TIA,,, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Error handling error # 1004 Run-time error | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Form Err.Raise error not trapped by entry procedure error handler | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming |