Ps. I should have said that UDF that are used in cells in worksheets can only
return values to the cells that hold that formula.

Functions used in your code (called by other functions/subs) can do what you
want.

But no you can't have a UDF in a cell call another function that tries to break
this. Excel is pretty smart.

Dave Peterson wrote:

User defined Functions return values to cells that hold the formula--they can't
touch the values in other cells.

Maybe it's time to look at worksheet events that can do what you want???

Bob A wrote:

I get an error 1004 when I execute the VBA function below. I've tried
everyting, Range = ID, cells(1) = ID, Range("test").Cells(1).Value = ID, you
name it. Always the same error.

Range "Test' is a named range of one cell on sheet 1 that contains the value
997.

The Function below reads the correct value out of the named range into the
var ID, but errors out when I write to the cell. Cell is not locked. Sheet
is not protected. Trust Center allows VBA code. Code is in a seperate
module, not on the sheet code.

Purpose is to increment a value, and save it in a cell on the sheet.

I call the function from another cell, like this: =IF(B4="","",NewID())

I've been at this for hours, researching what could be wrong. About ready to
toss the computer out the window. Anyone have any ideas?

Function NewID()
Dim ID As Long

ID = Range("Test").Cells(1).Value
ID = ID + 1
Range("Test").Cells(1).Value = ID 'This line throws the error

NewID = ID

End Function

--

Dave Peterson

--

Dave Peterson