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?
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 |
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 |