Worksheet Change Event Problem
All-
Solved the problem with hanging by adding a sheet reference:
Set getRange = Sheet9.Range("Year1_InspMon").Offset(0, k - 1)
Still need to trap the combo box change
Thanks for the help
Regards,
Tim
"Bob Phillips" wrote:
PS you might have to select each sheet and check it there, as it will not be
visible from other sheets if it is a sheet name.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Bob Phillips" wrote in message
...
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
|