+ Reply to Thread
Results 1 to 7 of 7

Macro for three-way link between cells

  1. #1
    Registered User
    Join Date
    02-24-2022
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    2

    Macro for three-way link between cells

    I want to connect three cells across three worksheets, so that when any of these three cells are updated, the update is reflected in the other two cells:
    • Sheet 1 B10
    • Sheet 2 B11
    • Sheet 3 B12

    I was able to use this macro for two-way linking but wasn't able to find something that worked for me for three-way linking:

    SHEET 1
    Please Login or Register  to view this content.

    SHEET 2
    Please Login or Register  to view this content.
    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. If you use them you don't need all of those INDENT tags. I have added them for you this time because you are a new member. --6StringJazzer

    I'm using Excel for Microsoft 365.
    Last edited by 6StringJazzer; 02-24-2022 at 03:04 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Macro for three-way link between cells

    I'm not sure why you have this test

    Please Login or Register  to view this content.
    Anyway, you simply extend this idea to three sheets:

    Sheet1

    Please Login or Register  to view this content.
    Sheet2

    Please Login or Register  to view this content.
    Sheet3

    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 02-24-2022 at 03:17 PM.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !


    To avoid a never ending story like in previous post you must first erase the VBA event Worksheet_Change in each worksheet module.

    Then paste this workbook event to the ThisWorkbook module :

    PHP Code: 
    Private Sub Workbook_SheetChange(ByVal Sh As ObjectByVal Target As Range)
        
    Dim VWXS&
            
    = [{"Sheet1","Sheet2","Sheet3"}]
            
    Application.Match(Sh.NameV0):  If IsError(WThen Exit Sub
            X 
    = [{"B10","B11","B12"}]:             If Target.Address(FalseFalse) <> X(WThen Exit Sub
            Application
    .EnableEvents False
        
    For 1 To UBound(V)
            If 
    <> W Then Sheets(V(S)).Range(X(S)).Value2 Target.Value2
        Next
            Application
    .EnableEvents True
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 02-24-2022 at 03:25 PM.

  4. #4
    Registered User
    Join Date
    02-24-2022
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    2

    Re: Try this !

    Marc L, your code worked! Thanks for your help.

    Can I ask how you would modify the code to apply to multiple cell links across sheets (e.g. B10 = B11 = B12, but also C10 = C11 = C12, etc.)? I made a few educated guesses without any success.

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Try this !

    Quote Originally Posted by Marc L View Post
    To avoid a never ending story like in previous post
    Oh, I'm sorry, that was terrible. Rookie error. My apologies.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Try this !


    Quote Originally Posted by janeDOE_142 View Post
    Can I ask how you would modify the code to apply to multiple cell links across sheets (e.g. B10 = B11 = B12, but also C10 = C11 = C12, etc.)? I made a few educated guesses without any success.
    Yes 'cause according to the initial post it was not designed for multiple cells by worksheet …

    As it depends on how many sheets, how many cells … the more the harder to hardcode and to maintain by yourself !
    An easy way is to not hardcode anything but to just use a 'Settings' - or 'Parameters' whatever - worksheet …

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Hardcoded sample …


    Quote Originally Posted by janeDOE_142 View Post
    e.g. B10 = B11 = B12, but also C10 = C11 = C12
    For such few sheets & cells :

    PHP Code: 
    Private Sub Workbook_SheetChange(ByVal Sh As ObjectByVal Target As Range)
        
    Dim VLCR&
            
    = [{"Sheet1","B10","C10";"Sheet2","B11","C11";"Sheet3","B12","C12"}]
        
    With Application
            L 
    = .Match(Sh.Name, .Index(V01), 0):                        If IsError(LThen Exit Sub
            C 
    = .Match(Target.Address(FalseFalse), .Index(VL0), 0):   If IsError(CThen Exit Sub
           
    .EnableEvents False
        
    For 1 To UBound(V)
            If 
    <> L Then If V(RC) > "" Then Sheets(V(R1)).Range(V(RC)).Value2 Target.Value2
        Next
           
    .EnableEvents True
        End With
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 02-25-2022 at 10:28 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro for pasting Link cells various sheets to Summary Sheet
    By srinivasan1965 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-09-2019, 08:01 AM
  2. Goal Seek Macro, link cells
    By AndrejW in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-28-2019, 07:15 PM
  3. Copy Paste Link All Cells in Row with Macro
    By chrisreisinger79 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-11-2016, 06:11 AM
  4. [SOLVED] Macro to link cells from two sheets after they are inserted
    By JohnMaier in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-06-2013, 06:16 PM
  5. vba/macro to paste link cells (file football)
    By win1980 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2012, 02:31 AM
  6. Need VB macro to link cells
    By Vandelay in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-06-2012, 09:03 PM
  7. [SOLVED] Create a Macro to Copy a Worksheet, and link certain cells...
    By jlstraw in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-23-2006, 08:40 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1