1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Running macros from vba code

Discussion in 'Ask an Excel Question' started by Divindunk, Nov 14, 2017.

  1. Divindunk

    Divindunk New Member

    Messages:
    6
    Hi

    I have placed the following code into a spreadsheet which works perfectly, however I have copied this code and changed the macros being called but the new vba code is still calling the old macro

    original code

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim changed As Range
    Set changed = Intersect(Target, Range("E9"))
    If Not changed Is Nothing Then
    If Target.Value = "" Then
    Call UnMergeCells1
    Exit Sub
    End If
    If IsNumeric(Target.Value) Then
    If Target.Address = Range("E9").Address Then
    Call MergeCells1
    Else
    Call MergeCells1
    End If
    Else
    Application.EnableEvents = False
    With Target
    .Select
    .ClearContents
    End With
    Application.EnableEvents = True
    End If
    End If
    Set changed = Nothing
    End Sub


    copied code (changes in bold)

    Private Sub Worksheet_Change2(ByVal Target As Range)
    Dim changed As Range
    Set changed = Intersect(Target, Range("E10"))
    If Not changed Is Nothing Then
    If Target.Value = "" Then
    Call UnMergeCells2
    Exit Sub
    End If
    If IsNumeric(Target.Value) Then
    If Target.Address = Range("E10").Address Then
    Call MergeCells2
    Else
    Call MergeCells2
    End If
    Else
    Application.EnableEvents = False
    With Target
    .Select
    .ClearContents
    End With
    Application.EnableEvents = True
    End If
    End If
    Set changed = Nothing
    End Sub


    Although I have changed the name of the code, cell references and the calls for macros the code is still calling for the original macros (numbered 1)

    Any help would be greatly appreciated
    Last edited: Nov 14, 2017
  2. vletm

    vletm Well-Known Member

    Messages:
    2,854
    Divindunk
    How would You run Your 'copied code'?
    Have You ever run it?

    Could You tried to rename
    Your 'original' Worksheet_Change to other name
    and Your 'copied code' to Worksheet_Change?
    ... and test it?
  3. Divindunk

    Divindunk New Member

    Messages:
    6
    Hi
    The original code runs automatically when data is entered into the cell E9. I didn't write this code I found it on the web and altered it to suit. It works fine and the re-numbered macros (which Merge cells and Unmerge cells and replace a formula) for the cells further down the document work ok as well (there are 23 further Merge and Unmerge macros (all working) for each row of the document and a corresponding 23 copies of the original code, the first copy is pasted above). But the copied code above which should run when the cell D10 has data entered into it, doesn't. When I run the code in the VBA window it stops and tries to run MergeCells1 instead of the MergeCells2 macro which is placed in the copied code. It may be there is a better way to achieve what I attempting

    Thanks
  4. vletm

    vletm Well-Known Member

    Messages:
    2,854
    Divindunk
    ... I tried to ask three questions ... and I got ... answers ... how many?

    1&2) How... /Have ... hmm ... You haven't run Your 'copied code'!
    3) Could ... if You couldn't then there would be a challenge!

    There are challenges to run
    Private Sub Worksheet_Change2(ByVal Target As Range)
    but
    Private Sub Worksheet_Change(ByVal Target As Range)
    is possible to run!
  5. Divindunk

    Divindunk New Member

    Messages:
    6
    Not sure I understand you!

    1. The original code runs when data is entered... the copied code should run the same way.

    2. I have run the copied code in the VBA window but it stops and looks for MergeCells1 instead of calling MergeCells2

    3. I have tried changing name of copied code with same result.
  6. Divindunk

    Divindunk New Member

    Messages:
    6
    Hi vietm

    Tried renaming again as you suggested and when the copied code has name without number it works? have tried leaving numbers of both but does not work. However I think I have found solution - incorporated copied code into original and it worked! Thanks very much. A good lesson in fault finding!

    Thanks again
  7. vletm

    vletm Well-Known Member

    Messages:
    2,854
    Divindunk
    There can be ONLY ONE
    Private Sub Worksheet_Change(ByVal Target As Range)
    which would work ...
    if there is even ONE letter difference ... it is not same!

    You haven't run that You 'copied code' ... You just modified it
    and
    next You still used the original code.
    That's why I asked ...
    How would You run Your 'copied code'?
    Have You ever run it?

    You can use ex 'breakpoints' to see which Marco-runs ...

Share This Page