Worksheet_Change on 2 sheets

[eoinymc]

Hi,

I have 2 drop down lists in 2 separate sheets, which have the same list associated with it. Question I'm looking for the answer for is in bold below?

So, if I change the value on one sheet, I want it to link to the other sheet and change..

I have the following code in the first sheet (Dashboard - The main sheet) The code setting the value equal to the other value is in between the Application.ScreenUpdating lines:

Private Sub Worksheet_Change(ByVal Target As Range)        Set TrendA = Sheets("Trend Analysis")
        
        Application.ScreenUpdating = False
        
        If (TrendA.Range("E2") <> Range("E4").Value) Then
            TrendA.Range("E2") = Range("E4").Value
        End If
        
        Application.ScreenUpdating = True
     
        If Range("BM48") = 1 Then
            MsgBox " Please enter either % OR Value.", vbExclamation, "Avg Inc."
            Range("BG48:BL48").ClearContents
            Range("BG48").Select
        End If
        
        FormatGraph ("Front")
     
End Sub

So, this obviously runs a little bit more code after it, which is needed for some other things

Then, I have this in the other sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
        Set Dashb = Sheets("Dashboard")
        Set TrendA = Sheets("Trend Analysis")
        
        Application.ScreenUpdating = False
        
        If (Dashb.Range("E4") <> TrendA.Range("E2").Value) Then
            Dashb.Range("E4") = TrendA.Range("E2").Value
        End If
        
        TrendA.Activate
        
        Application.ScreenUpdating = True
     
End Sub

The only thing this does is set the value as the same on the Dashboard and activates this sheet again. The reason I have the activate line in there is because it runs the Worksheet_Change on the Dashboard when this changes (obviously!)

So, what happens is that it still changes sheets and back again when I change either value...it's not the end of the world, but can anyone tell me why is it showing the sheet changing when I have application.ScreenUpdating set to False?

If I'm not clear, please let me know!

Thanks,

Eoin

 

[JLeno]

I am not sure what your exact problem is. Ultimately, the new value should be shown because you have application.screenupdating = true at the end of your code, right?

I have tried your code and my Excel screen flashes (because of the ScreenUpdating turning of and on again), but it does not show the Dashboard sheet when i'm on the Trend Analysis sheet.

In fact, if I delete (in both sheets' code) the Application.ScreenUpdating lines, and the TrendA.Activate line, the Events work without flashing screens.

I hope this works for you too?

 

[eoinymc]

I figured that it is the FormatGraph function that I run on Change...anyone got any ideas how I can modify the following code to ensure that it doesn't activate the other sheet?

Function FormatGraph(myWorksheet As String)

Set Dashb = Sheets("Dashboard")
Set Ind = Sheets("Index")


Application.ScreenUpdating = False


Dashb.ChartObjects("RevWfall").Activate


    Dim myPoint As Integer, valArray


    With Excel.ActiveChart.SeriesCollection(2)
        valArray = .Values
        j = 36
        For myPoint = 2 To 6 '.Points.Count
            If (Ind.Cells(j, 39) - Ind.Cells(j - 1, 39)) < 0 Then
                    With .Points(myPoint)
                        .Interior.Color = RGB(255, 204, 0)
                    End With
                    j = j + 1
            Else
                    With .Points(myPoint)
                        .Interior.Color = RGB(150, 150, 150)
                    End With
                    j = j + 1
            End If
        Next
    End With
    
    With Excel.ActiveChart.SeriesCollection(3)
        valArray = .Values
        j = 36
        For myPoint = 2 To 6 '.Points.Count
            If (Ind.Cells(j, 39) - Ind.Cells(j - 1, 39)) < 0 Then
                    With .Points(myPoint)
                        .Interior.Color = RGB(255, 204, 0)
                    End With
                    j = j + 1
            Else
                    With .Points(myPoint)
                        .Interior.Color = RGB(150, 150, 150)
                    End With
                    j = j + 1
            End If
        Next
    End With
    
ActiveChart.Axes(xlValue).MinimumScale = Ind.Range("AM43")


Dashb.ChartObjects("S3Wfall").Activate


    With Excel.ActiveChart.SeriesCollection(2)
        valArray = .Values
        j = 6
        For myPoint = 2 To 5 '.Points.Count
            If (Ind.Cells(j, 39) - Ind.Cells(j - 1, 39)) < 0 Then
                    With .Points(myPoint)
                        .Interior.Color = RGB(255, 204, 0)
                    End With
                    j = j + 1
            Else
                    With .Points(myPoint)
                        .Interior.Color = RGB(150, 150, 150)
                    End With
                    j = j + 1
            End If
        Next
    End With
    
       With Excel.ActiveChart.SeriesCollection(3)
        valArray = .Values
        j = 6
        For myPoint = 2 To 5 '.Points.Count
            If (Ind.Cells(j, 39) - Ind.Cells(j - 1, 39)) < 0 Then
                    With .Points(myPoint)
                        .Interior.Color = RGB(255, 204, 0)
                    End With
                    j = j + 1
            Else
                    With .Points(myPoint)
                        .Interior.Color = RGB(150, 150, 150)
                    End With
                    j = j + 1
            End If
        Next
    End With
    
ActiveChart.Axes(xlValue).MinimumScale = Ind.Range("AM12")


SendKeys "{ESC}"


Application.ScreenUpdating = True


End Function

Cheers..

Eoin