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

Thread: Run a macro when a cell changes on a different tab?

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

    Default

    In sheet3, I have:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Set Target = Cells(1, 1)
    If Target = Cells(47, 2) Then
    MessageBoxMania
    End If
    End Sub

    And in my main module I have:

    Sub MessageBoxMania()
    Dim Resp As Integer
    Resp = MsgBox("message", 4, "Please confirm")
    If Resp = 6 Then
    'macro
    Else
    End If
    End Sub

    So if Cell(47,2)=1, it runs the macro perfectly, but ONLY if I am on sheet3. If I am on another sheet, and the value of Cell(47,2) changes to 1, the macro will not run. Eventually, I would like it to work like this:

    While on some other sheet. Value on sheet3 changes. Save current position. Go to sheet3 and run macro. Return to previous position. Sounds complicated, but I almost have everything.
    Thanks!

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Private Sub Worksheet_Change(ByVal Target As Range)
    Set Target = Cells(1, 1)
    If Target = Cells(47, 2) Then
    MessageBoxMania
    End If
    End Sub
    Try this instead:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Set Target = Cells(1, 1)
    If Target = Cells(47, 2) Then
    call MessageBoxMania
    End If
    End Sub

    _________________
    Hope this helps.
    Kind regards, Al.

    [ This Message was edited by: Al Chara on 2002-04-11 13:22 ]

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
  •