Howdy,
I am looking at one cell defined as "output_period", and when it changes I
want to update the sheet with a range from another sheet. I have a couple of
questions;
1) the output_period is controlled by a Forms combo-box, and the macro does
not seem to fire based on that change. Is there another event I should
consider (preference would be not to use ActiveX).
2) normal cell editing does cause the macro to fire, however it hangs at the
"set getRange" line. Method 'Range' of object '_worksheet' failed. I have
used similar code in non-event type macros without a problem, and I am not
sure what is wrong.

Here is the code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

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

k = Range("output_period")

If Not Intersect(Target, Me.Range("output_period")) Is Nothing Then
Set getRange = Range("Year1_inspMon").Offset(0, k - 1)
Set outRange = Range("out_inspect_loc")

getRange.Copy Destination:=outRange

End If

End Sub

TIA,
Tim

Hey Tim,

If you call your code from the bit which says 'do something' and change the
range to your desired range that should solve (1).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing
Then
' do something'
End If
End Sub

As for why it doesn't run, are you sure that your named range output_period
exists? are you also sure that Year1_inspMon is a named range?

Regards

Andi


"tim" wrote in message
...
Howdy,
I am looking at one cell defined as "output_period", and when it changes I
want to update the sheet with a range from another sheet. I have a couple
of
questions;
1) the output_period is controlled by a Forms combo-box, and the macro
does
not seem to fire based on that change. Is there another event I should
consider (preference would be not to use ActiveX).
2) normal cell editing does cause the macro to fire, however it hangs at
the
"set getRange" line. Method 'Range' of object '_worksheet' failed. I
have
used similar code in non-event type macros without a problem, and I am not
sure what is wrong.

Here is the code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

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

k = Range("output_period")

If Not Intersect(Target, Me.Range("output_period")) Is Nothing Then
Set getRange = Range("Year1_inspMon").Offset(0, k - 1)
Set outRange = Range("out_inspect_loc")

getRange.Copy Destination:=outRange

End If

End Sub

TIA,
Tim

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

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

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

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

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

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

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

All,

Solved the problem with hanging by adding a sheet reference:
Set getRange = Sheet9.Range("Year1_InspMon").Offset(0, k - 1)

Solved the Combo box issue by eliminating it and going to dta validation.
Credit to www.contextures.com/

Thanks for the input, hope this helps others down the road...

Regards,
Tim


"tim" wrote:

Howdy,
I am looking at one cell defined as "output_period", and when it changes I
want to update the sheet with a range from another sheet. I have a couple of
questions;
1) the output_period is controlled by a Forms combo-box, and the macro does
not seem to fire based on that change. Is there another event I should
consider (preference would be not to use ActiveX).
2) normal cell editing does cause the macro to fire, however it hangs at the
"set getRange" line. Method 'Range' of object '_worksheet' failed. I have
used similar code in non-event type macros without a problem, and I am not
sure what is wrong.

Here is the code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

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

k = Range("output_period")

If Not Intersect(Target, Me.Range("output_period")) Is Nothing Then
Set getRange = Range("Year1_inspMon").Offset(0, k - 1)
Set outRange = Range("out_inspect_loc")

getRange.Copy Destination:=outRange

End If

End Sub

TIA,
Tim