range object error
Using excel 2003 I put the following code into this wrokbook and dcid not get
any error. I tested the code by putting c hanges into sheet 1, 2, & 3. Try
openning a new workbook (close all excel workbooks) and run this code. I bet
you don't get an error. Do you have any protections set in your workbook?
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
' runs when a sheet is changed
ActiveWorkbook.Names.Add Name:="WHData", _
RefersTo:=ActiveWorkbook.Worksheets("Sheet1").Rang e("$B$5:$E$8")
End Sub
"BigJimmer" wrote:
Yes, I saw those. I used the code that Tom provided without an issue until
I tried to reference it from ThisWorkbook.Workbook_SheetChange.
There is something about it being referenced from this module that is a
problem, or maybe it's just me, as even this simple hard coded logic withing
this module generates the same error -
ActiveWorkbook.Names.Add Name:="WHData", _
RefersTo:=ActiveWorkbook.Worksheets("Sheet1").Rang e("$B$5:$E$8")
"Joel" wrote:
There is no sheet reference. Notice the periods in front of Range and the
two Cells.
Sub ABC()
Row = 3
col = 12
MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
with sheets("Sheet1")
ActiveWorkbook.Names.Add Name:="WHData", _
RefersTo:=.Range(.Cells(Row, col), _
.Cells(Row + UBound(MyArray) - 1, col + 3))
end with
End Sub
"BigJimmer" wrote:
I am successful in running the provided routine directly from a standard
module, and even from a worksheet module. When I attempt to call the same
code from ThisWorkbook.Workbook_SheetChange event, I get the application
error.
I'm using the version provided that specifies the worksheet.
"Tom Ogilvy" wrote:
Sub ABC()
Row = 3
col = 12
MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
ActiveWorkbook.Names.Add Name:="WHData", _
RefersTo:=Range(Cells(Row, col), _
Cells(Row + UBound(MyArray) - 1, col + 3))
End Sub
In a general module worked fine for me. If it is in a sheet module then
you need to use something like
Sub ABC()
Row = 3
col = 12
MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
With worksheets("Sheet1")
ActiveWorkbook.Names.Add Name:="WHData", _
RefersTo:=.Range(.Cells(Row, col), _
.Cells(Row + UBound(MyArray) - 1, col + 3))
End With
End Sub
so you are sure all range references are on the same page.
Otherwise, make sure your references form a legal address.
--
regards,
Tom Ogilvy
"BigJimmer" wrote:
I'm using Excel 2000, and trying to modify a range name within VBA. When I
try code such as this -
Dim row, col as integer
row = 1
col = 1
ActiveWorkbook.Names.Add Name:="WHData", _
RefersTo:= Range(Cells(row, col), Cells(row + UBound(MyArray) - 1, col +
3))
I get 1004 Application-defined or object-defined error. I have confirmed
that UBound(MyArray) is defined, and in my code I have logic that changes
the values of row and col.
To test, I used the following, and did not get this error -
ActiveWorkbook.Names.Add Name:="WHData", RefersTo:= Range("A1:C5")
What am I missing to be able to use cells(rwindex, colindex) to set the range?
Thanks!
|