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.

 

[NateO]

Not sure if my answer is pertinent, I assumed AA is a column versus a row. And I assume, Bob, that you're shifting columns versus rows, based on your references...

_________________
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 18:38

 

[NateO]

I may need to streamline this a little:

Dim coL As Range
Set coL = Range("myClm")
Worksheets(coL.Worksheet.Name).Range(coL)).Delete

I think the integer definition would delete the row versus the column.

Or perhaps:

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

_________________
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 19:21

 

[bobmc]

On 2002-04-04 17:29, Jay Petrulis wrote:
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

Yee Gads! Have I ever screwed this up! You're absolutely right...I want to adjust for ROW deletion and insertion, not COLUMN.

That's probably why I have the question below...

I'm absolutely clueless when it comes to code, so please forgive my ignorance here.

I get the "module" part of this, as it pertains to relative values. But the "worksheet_change" has me a little confused. We're still referring to absolutes when we call out numbers like "13, 14 & 15" do we not? So if I delete row 8, do 13, 14 & 15 change to 12, 13 & 14?.

Also, the column range throws me a little. Why are these values listed as numbers instead of letters? And should I set the column range so it covers the entire width of my spreadsheet? (Obviously if I'm deleting or inserting a row, it does so over the entire width of the sheet.)

Thanks for all your help, guys.
This message was edited by bobmc on 2002-04-04 22:39

 

[NateO]

I must admit, I was quite thrown off, but I think I'm following you now. You'll want to highlight the first of your three rows in question, insert the name myrow. Use absolute references. Then you'll want to right-click the sheet in question and paste the following code (this will clear the entire row):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Integer, p As Integer, o As Integer, rw As Range
Set rw = Range("myrow")
n = rw.Row
p = n + 1
o = n + 2
If Target.Address = Range("ae" & n).Address Then _
Range(p & ":" & o).ClearContents
If Target.Address = Range("ae" & p).Address Then _
Range(Range(n & ":" & n), Range(o & ":" & o)).ClearContents
If Target.Address = Range("ae" & o).Address Then _
Range(n & ":" & p).ClearContents
End Sub

Where n is the first row, p is the next row down and o is third row down. Incidentally, also my initials .

The code below will just clear the contents of the 'other two' cells in question in column AE:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Integer, p As Integer, o As Integer, rw As Range
Set rw = Range("myrow")
n = rw.Row
p = n + 1
o = n + 2
If Target.Address = Range("ae" & n).Address Then _
Range("ae" & p & ":" & "ae" & o).ClearContents
If Target.Address = Range("ae" & p).Address Then _
Range("ae" & n & "," & "ae" & o).ClearContents
If Target.Address = Range("ae" & o).Address Then _
Range("ae" & n & ":" & "ae" & p).ClearContents
End Sub

Either way, inserting or deleting rows above myrow will offset the three rows. Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by nateo on 2002-04-05 11:45

 

[NateO]

I think I'll need a vacation after this post. But I think the best way to do this is to actually define a cell, not a row. That way if you insert or delete either columns or rows, your 3-cell area will update. Pick the top cell of the 3 and name it mycell. Then right-click on the worksheet in question and place the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = Range("mycell").Address Then
Range(Range("mycell").Offset(1).Address).ClearContents
Range(Range("mycell").Offset(2).Address).ClearContents
End If
If Target.Address = Range(Range("mycell").Offset(1).Address).Address Then
Range(Range("mycell").Address).ClearContents
Range(Range("mycell").Offset(2).Address).ClearContents
End If
If Target.Address = Range(Range("mycell").Offset(2).Address).Address Then
Range(Range("mycell").Address).ClearContents
Range(Range("mycell").Offset(1).Address).ClearContents
End If
Application.EnableEvents = True
End Sub

 

[Jay Petrulis]

On 2002-04-05 13:02, NateO wrote:
I think I'll need a vacation after this post. But I think the best way to do this is to actually define a cell, not a row. That way if you insert or delete either columns or rows, your 3-cell area will update. Pick the top cell of the 3 and name it mycell. Then right-click on the worksheet in question and place the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = Range("mycell").Address Then
Range(Range("mycell").Offset(1).Address).ClearContents
Range(Range("mycell").Offset(2).Address).ClearContents
End If
If Target.Address = Range(Range("mycell").Offset(1).Address).Address Then
Range(Range("mycell").Address).ClearContents
Range(Range("mycell").Offset(2).Address).ClearContents
End If
If Target.Address = Range(Range("mycell").Offset(2).Address).Address Then
Range(Range("mycell").Address).ClearContents
Range(Range("mycell").Offset(1).Address).ClearContents
End If
Application.EnableEvents = True
End Sub

Hi,

Yet *another* way. I believe that this will handle all scenarios, unless a single cell gets shifted and causes the range to be not a single column/row.

1. Define the range. I called it "MyRange" here. The referencing was absolute.

2. Enter the following in the sheet module
--------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim isect, temp

Application.EnableEvents = False

Set isect = Application.Intersect(Range("MyRange"), Target)
If Not isect Is Nothing Then
temp = Target.Value
Range("MyRange").ClearContents
Target = temp
End If
Application.EnableEvents = True

End Sub
----------------------

This takes the entered value and stores it to the temp variable. The entire range is then cleared and the temp variable is placed in the target cell.

This will work if rows and columns are inserted above/below/right/left or inside the range (e.g. MyRange is AE10:AE20 and a new row 12 is added, MyRange becomes AE10:AE21).

Bye,
Jay

 

[NateO]

Likin' this Jay. Kudos.

Have a great weekend!