Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Run Macro when a certain cell value is changed (Excel 2002)

    Hi,
    I have a macro that copies and paste a database from one worksheet to another... instead of calling this macro with a command button, I'd like the macro to run when the cell value in Range("F2") changes. I'm stuck... here's what I got... which isn't much.
    Thanks!!
    Lana

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("F2")
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Run Macro when a certain cell value is changed (Excel 2002)

    Try this:
    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("F2")) Is Nothing Then
    'Put your code here
    'or call your routine from here
    MsgBox "F2 was changed!"
    End If
    End Sub</pre>


    It needs to be the change event, not selection change. The selection change is triggered when the selection is changed, not a cell value. The "Target" is the range that is changed so the code checks to see if F2 is in that "target" using intersect. If the intersect is not nothing (F2 does intersect the target) then the code would run.

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run Macro when a certain cell value is changed

    Hi Lana

    How about this:

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Intersect(ActiveCell, Range("F2")) Is Nothing Then

    Else
    Range("A1:B1").Copy Destination:=Range("A2")

    End If

    End Sub
    </pre>


    I'll let you change the range values for the copy and paste
    Jerry

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Run Macro when a certain cell value is changed (Excel 2002)

    Thanks Steve... this works great... and thanks for the explanation as well... this helps me to continue learning!
    Lana

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Run Macro when a certain cell value is changed

    Thanks Jerry... this works great as well... thanks for helping me out, I appreciate it!!
    Lana

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run Macro when a certain cell value is changed

    Worksheet_SelectionChange runs whenever the user moves to another cell, whether the value of the original cell has changed or not. To monitor changing cell values, you need Worksheet_Change.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Macro when a certain cell value is changed

    Jerry's routine does not do what you asked. It runs when the selection changes and not when a cell value is changed. It then checks to see what the current active cell is and if it is F2 would run your code. If F2 is the active cell, then the selection has just changed to F2, and therefore it could not have changed yet. Use Steve's code if you really want to do what you asked.
    Legare Coleman

Posting Permissions

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