Bob,

Near as I can tell, the range name is global (according to Name Manager).
Here is the definition =OFFSET(InspMon_Result!$B$19,0,0,Num_Inspect_Max,1 ).
Now the initila reference for this range name appears to be sheet specific,
but I am not sure how one gets around that. Num_Inspect_Max is also global.
Again, using this reference in a non-worksheet macro works fine.

Using Excel2003, if it matters.

Regards,
Tim

"Bob Phillips" wrote:

Tim,

I suppose the name could be a worksheet name not workbook. Go into
InsertNameDefine Name and select that item. If it has a sheet name
alongside it in the listbox, it is a sheet name. Delete it and re-create as
a workbook name.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tim" wrote in message
...
Bob,

Thanks for the input. I will look at "trapping". Reagrding the "hang";
Output_range is 1 to 200 =k, so the worse that offset would be is 0,0.
The
ranges exist, I use them in several locations, workbook and macros.

Since this is a worksheet event, does it matter that
Range("Year1_InspMon")
is not on that worksheet? If so, how do I fix it?

Regards,
Tim

"Bob Phillips" wrote:

This is the same as a Data Validation in Excel 97, and the way I get
around
that is to have (yet) another cell reference the control's linked cell,
and
use the worksheet calculate event to trap it. Something like

Private prevcell

Private Sub Worksheet_Calculate()
If Me.Range("H10").Value < prevcell Then
MsgBox Me.Range("H10").Value
prevcell = Me.Range("H10").Value
End If
End Sub

As to the other one, that sounds like the range does not exits or maybe
the
k-1 offset is generating a negative value.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tim" wrote in message
...
Andi,

Thanks for the feedback, but I don't see the real difference between
what
you posted versus what I have. I think I incorporated your
suggestions,
with no change in the result. Yes, Output_Period and Year1_Inspmon
are
both
defined ranges. I have been using them in other "normal" module code.
Still
hangs at the same line and still does not fire when the target cell is
change
from a Form combo box. Here is what I changed, if I missed somthing,
please
let me know...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim getRange As Range
Dim outRange As Range
Dim k As Integer

If Not Application.Intersect(Target, Range("B3")) Is Nothing Then
k = Range("Output_Period")
Set getRange = Range("Year1_InspMon").Offset(0, k - 1)
Set outRange = Range("Out_Inspect_Loc")

getRange.Copy Destination:=outRange

End If

End Sub