External workbook

[rickblunt]

Greetings, I have been using this code to change a shape color and it works fine, but now instead of the same workbook/worksheet I would like to source it from a different workbook. I am having trouble referencing that second workbook, every one I have tried has errored out. So I am thinking that the problem is my syntax. Could anyone show me the syntax to get it to reference the following workbook? Thank you

"'L:\CommonRW\[FacilitiesApps.xlsm]Coding'!$E7"

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("F7")) Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) Then
        If Target.Value = 3 Then
            ActiveSheet.Shapes("Rectangle 2").Fill.ForeColor.RGB = vbRed
        ElseIf Target.Value = 2 Then
            ActiveSheet.Shapes("Rectangle 2").Fill.ForeColor.RGB = vbYellow
        Else
            ActiveSheet.Shapes("Rectangle 2").Fill.ForeColor.RGB = vbGreen
        End If
    End If
End Sub

 

[Rijnsent]

Hi Rick,
what are you trying to achieve? Pick up the value of E7 in that external file? Or do you want to change something in the current workbook when something gets changed in the external workbook?
Cheers,
Koen

 

[rickblunt]

Hi Rick,
what are you trying to achieve? Pick up the value of E7 in that external file? Or do you want to change something in the current workbook when something gets changed in the external workbook?
Cheers,
Koen

Hi Rijnsent - I am trying to update the active workbook from the values of the second workbook on the server, so that a shape fill color will change based on the cell value from the external worksheet. (In this workbook I have a sub that is "refreshing the data" every 30 minutes by opening/closing all of the external workbooks).

Typically I use hyperlinks and just bring the data into a "coding"' worksheet in the same workbook that I use to keep all of the data synced up. If I was changing cell colors then I could just use conditional formatting to do that, but now I am trying to change the shape colors. I have a sub that can do this based on a cell value, but I don't believe that it will work because the reference cell actually has the hyperlink in it, and not just the returned cell value.

I realize that the worksheet change event may not trigger the change, but I thought I would try this first and then tweak the trigger after I got all of my references lined up. This code works fine in the same sheet, it just stops working if I put it on my second sheet or another workbook. Right now it appears that the typical workbook/worksheet style syntax is not correct because I get errors, so I thought I would start with that and get it correct first.