Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Run Macro when columns change

  1. #1
    New Member
    Join Date
    Jan 2002
    Location
    New Zealand
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, Can somebody please help me?
    I was wanting to get some code that will run a macro when any cell from 2 specific columns changes.

    Also I have 2 sheets to do this for, (Sheets are set up identically) is it possible to run the same macro on both sheets if one cell from the two sets of columns changes?

  2. #2
    New Member
    Join Date
    Apr 2002
    Location
    Vacaville
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,

    There is a way but I will need more info from you to send you the exact code you will need. Please email me at foss20@msn.com


    Mark Foss

  3. #3
    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

    Below is code you place in your "ThisWorkbook" module. Right-click on the Excel Icon the left of the Menu heading "File." Select "View Code." Paste the following code into the module:

    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Index = 1 Then     ' 1'st sheet in workbook
        If Target.Column = 3 Or Target.Column = 4 Then  ' 3'rd and 4th colums are active
        Application.Run ("MyMacro") 'Change 'MyMacro to your Macro's name
        End If
    End If
    If Sh.Index = 2 Then     ' 2'st sheet in workbook
        If Target.Column = 3 Or Target.Column = 4 Then  ' 3'rd and 4th colums are active
        Application.Run ("MyMacro") 'Change 'MyMacro to your Macro's name
        End If
    End If
    End Sub
    Change the sheet index numbers where appropriate and/or column numbers. If you prefer to use sheet name's, you can change the code to read like:

    Code:
    Sh.name = "mySheet" "Case [very] sensitive
    If there are questions, post back. Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-30 15:36 ]

  4. #4
    New Member
    Join Date
    Jan 2002
    Location
    New Zealand
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the code NateO,

    It works a treat.

    Cheers,

    James.

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
  •