dynamic VB?

bobmc

Board Regular
Joined
Mar 13, 2002
Messages
142
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:

Code:
Dim n As Integer, coL As Range
Set coL = Range("myClm")
n = coL.column
Worksheets(coL.Worksheet.Name).Range(n & ":" & n).Delete
 
Upvote 0
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:

Code:
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
 
Upvote 0
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-04 17:45
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top