As you expected, when I copied the code to a new workbook, it worked fine.

There is no protection on the existing document yet, as that is the last
step I was going to do.

Any idea as to why this doesn't work in the existing workbook?

Thanks!

"Joel" wrote:

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!