dynamic VB?

[bobmc]

I have a spreadsheet that utilizes VB code for specific cells. I've noticed that when I delete or insert a row above the cells affected by VB, the code does not change to reflect the relative change in cell numbers, so I've been fixing the code manually.

Any way to automate this?

Thanks.

[willlobb]

what is your code?
Will

[bobmc]

Here's a snippet of it. There are 20 - 30 other instances of this in the code.
**************
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$AE$13" Then [AE14,AE15].ClearContents
If Target.Address = "$AE$14" Then [AE13,AE15].ClearContents
If Target.Address = "$AE$15" Then [AE13,AE14].ClearContents
End Sub
*****************

Basically, the code just toggles a selection between three cells.

So, is it possible to make this dynamic so that if I delete say, row AA, these codes would automatically change from "AE" to "AD"?

Any help is greatly appreciated.

[ This Message was edited by: bobmc on 2002-04-04 15:48 ]

[bobmc]

Anyone?

[Jay Petrulis]

On 2002-04-04 15:49, bobmc wrote:
Anyone?

I don't think this is possible, because I keep getting caught in an endless loop when I clear the contents in the "other cells"

The entry in cell 1 clears the other two, but that clearing triggers the worksheet_change event again and again.

Here is what I have so far. Possibly otehrs can take this and run with it.

-------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 26 And Target.Column < 32 Then
If Target.Row = 13 Then
Cells(14, Target.Column).ClearContents
Cells(15, Target.Column).ClearContents
Application.EnableEvents = False
Exit Sub
End If
If Target.Row = 14 Then
Cells(13, Target.Column).ClearContents
Cells(15, Target.Column).ClearContents
Application.EnableEvents = False
Exit Sub
End If
If Target.Row = 15 Then
Cells(13, Target.Column).ClearContents
Cells(14, Target.Column).ClearContents
Application.EnableEvents = False
Exit Sub
End If
End If
Application.EnableEvents = True
End Sub
--------------------------

This may get someone started.

Bye,
Jay

[Jay Petrulis]

Hi,

I spoke too soon. This worked for me:

In a normal code module, place the following three subs

----------------
Sub clear1(sValue)
Application.EnableEvents = False
Range(sValue).Offset(1, 0).ClearContents
Range(sValue).Offset(2, 0).ClearContents
Application.EnableEvents = True
End Sub

Sub clear2(sValue)
Application.EnableEvents = False
Range(sValue).Offset(-1, 0).ClearContents
Range(sValue).Offset(1, 0).ClearContents
Application.EnableEvents = True
End Sub

Sub clear3(sValue)
Application.EnableEvents = False
Range(sValue).Offset(-2, 0).ClearContents
Range(sValue).Offset(-1, 0).ClearContents
Application.EnableEvents = True
End Sub
-----------------

In your worksheet_change event place the following:

---------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 26 And Target.Column < 32 Then
If Target.Row = 13 Then
Call Module2.clear1(Target.Address)
End If
If Target.Row = 14 Then
Call Module2.clear2(Target.Address)
End If
If Target.Row = 15 Then
Call Module2.clear3(Target.Address)
End If
End If
End Sub
--------------------

Change the target.column range to suit your needs.

Post back if this doesn't work for you.

Bye,
Jay

[NateO]

To respond to the last part, naming the column may be of serious use (with non-absolute references) (instert->names->define). Say you name the column "myClm", then you can try and use a procedure like:

Dim n As Integer, coL As Range
Set coL = Range("myClm")
n = coL.column
Worksheets(coL.Worksheet.Name).Range(n & ":" & n).Delete

[Jay Petrulis]

On 2002-04-04 17:30, NateO wrote:
To respond to the last part, naming the column may be of serious use (with non-absolute references) (instert->names->define). Say you name the column "myClm", then you can try and use a procedure like:

Dim n As Integer, coL As Range
Set coL = Range("myClm")
n = coL.column
Worksheets(coL.Worksheet.Name).Range(n & ":" & n).Delete

Hi NateO,

"To respond to the last part,..." were you referring to my first attempt or the second attempt (or both, or the OP)? No matter, because that was clever.

Awesome suggestion! I think it would work nicely.

Bye,
Jay

[NateO]

Hi Jay, Thanks for the kudos! As always, don't mean to steal anyone's thunder, just thinking out loud. I was actually responding to:

So, is it possible to make this dynamic so that if I delete say, row AA, these codes would automatically change from "AE" to "AD"?

My brain-storming began before I noticed your post. It seems to me one could shift this way.

Unfortunately, I'm at a thin-client without Excel, so it's hard for me to comment on your code, but having seen a post or two of yours (Jay), I suspect it works.

Have a good eve.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-04-04 17:45 ]

[Jay Petrulis]

On 2002-04-04 17:38, NateO wrote:
Hi Jay, don't mean to steal anyone's thunder, just thinking out loud. I was actually responding to:

So, is it possible to make this dynamic so that if I delete say, row AA, these codes would automatically change from "AE" to "AD"?

It seems to me one could shift this way.

Unfortunately, I'm at a thin-client without Excel, so it's hard for me to comment on your code, but having seen a post or two of yours (Jay), I suspect it works.

Have a good eve.

No thunder to be stolen here. I think it would be a great solution, and if it doesn't work alone, it can be combined with my suggestions to make a robust option for the OP.

I'm outta here!
Jay