Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: dynamic VB?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    what is your code?
    Will

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anyone?

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •