+ Reply to Thread
Results 1 to 12 of 12

VBA Code for Discreet 2 Way Link Between Cells on Same Sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    03-21-2021
    Location
    US
    MS-Off Ver
    365
    Posts
    4

    VBA Code for Discreet 2 Way Link Between Cells on Same Sheet

    Hi folks.
    I wish to create discreet two-way links between two ranges of cells in the same sheet. Range 1 is A41:A56 and Range 2 is A61:A76. I would like to create a situation in which whenever A41 is changed, it also changes A61, and vice versa. Ditto for A42 changing and changed by A57...and so on. I've tried a variety of methods using worksheet_change, but am having trouble getting the bi-directionality to work. Also, when I change any of the A41-A56 cells, it produces the same change in the entire Range 2 group, rather than the single cell that is its counterpart. Any help with this would be greatly appreciated.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,386

    Re: VBA Code for Discreet 2 Way Link Between Cells on Same Sheet

    Paste in Sheet1 :

    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    If Not Application.Intersect(Range(Target.Address), Range("A2:D5")) Is Nothing Then '<--- change range as required
        Application.EnableEvents = False
            Sheets(1).Range(Target.Address).Value = Target '<-- change Sheet # as required
        Application.EnableEvents = True
    End If
    End Sub

    Paste in Sheet2 :

    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    If Not Application.Intersect(Range(Target.Address), Range("A2:D5")) Is Nothing Then  '<--- change range as required
        Application.EnableEvents = False
            Sheets(2).Range(Target.Address).Value = Target '<-- change Sheet # as required
        Application.EnableEvents = True
    End If
    End Sub

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,024

    Re: VBA Code for Discreet 2 Way Link Between Cells on Same Sheet

    @Logit: did you miss this ...
    ... two-way links between two ranges of cells in the same sheet.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    MS365 V.2406
    Posts
    2,307

    Re: VBA Code for Discreet 2 Way Link Between Cells on Same Sheet

    Possibly...
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rg1 As Range, rg2 As Range
    
        Set rg1 = Me.Range("a41:a56")
        Set rg2 = rg1.Offset(20, 0)
        Application.EnableEvents = False
        If Not Intersect(Target, rg1) Is Nothing Then Target.Offset(20, 0) = Target
        If Not Intersect(Target, rg2) Is Nothing Then Target.Offset(-20, 0) = Target
        Application.EnableEvents = True
        
    End Sub

  5. #5
    Registered User
    Join Date
    03-21-2021
    Location
    US
    MS-Off Ver
    365
    Posts
    4

    Re: VBA Code for Discreet 2 Way Link Between Cells on Same Sheet

    Sorry for taking so long to reply. Thank you so much. The above code works great. However....

    What if I have another range of cells that I want to add to the above cells so that all three update simultaneously, and entry into any of them will update the other two? I tried adapting your code, but it wasn't updating for the new range that was added:

    Private Sub Worksheet_Change(ByVal Target As Range)
        
    
    Dim solo41 As Range, solo61 As Range, solo157 As Range
    
        Set solo41 = Me.Range("a41:a56")
        Set solo61 = solo41.Offset(20, 0)
        Application.EnableEvents = False
        If Not Intersect(Target, solo41) Is Nothing Then Target.Offset(20, 0) = Target
        If Not Intersect(Target, solo61) Is Nothing Then Target.Offset(-20, 0) = Target
        Application.EnableEvents = True
        
        Set solo41 = Me.Range("a41:a56")
        Set solo157 = solo41.Offset(20, 0)
        Application.EnableEvents = False
        If Not Intersect(Target, solo41) Is Nothing Then Target.Offset(116, 0) = Target
        If Not Intersect(Target, solo157) Is Nothing Then Target.Offset(-116, 0) = Target
        Application.EnableEvents = True
    
    End Sub
    Last edited by AliGW; 04-05-2021 at 10:32 AM.

  6. #6
    Registered User
    Join Date
    03-21-2021
    Location
    US
    MS-Off Ver
    365
    Posts
    4

    Re: VBA Code for Discreet 2 Way Link Between Cells on Same Sheet

    Sorry for taking so long to reply. Thank you so much. The above code works great. However....

    What if I have another range of cells that I want to add to the above cells so that all three update simultaneously, and entry into any of them will update the other two? I tried adapting your code, but it wasn't updating for the new range that was added:

    Private Sub Worksheet_Change(ByVal Target As Range)
        
    
    Dim solo41 As Range, solo61 As Range, solo157 As Range
    
        Set solo41 = Me.Range("a41:a56")
        Set solo61 = solo41.Offset(20, 0)
        Application.EnableEvents = False
        If Not Intersect(Target, solo41) Is Nothing Then Target.Offset(20, 0) = Target
        If Not Intersect(Target, solo61) Is Nothing Then Target.Offset(-20, 0) = Target
        Application.EnableEvents = True
        
        Set solo41 = Me.Range("a41:a56")
        Set solo157 = solo41.Offset(20, 0)
        Application.EnableEvents = False
        If Not Intersect(Target, solo41) Is Nothing Then Target.Offset(116, 0) = Target
        If Not Intersect(Target, solo157) Is Nothing Then Target.Offset(-116, 0) = Target
        Application.EnableEvents = True
    
    End Sub

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,265

    Re: VBA Code for Discreet 2 Way Link Between Cells on Same Sheet

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [code] [/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. As you are new, I have done it for you today.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  8. #8
    Registered User
    Join Date
    03-21-2021
    Location
    US
    MS-Off Ver
    365
    Posts
    4

    Re: VBA Code for Discreet 2 Way Link Between Cells on Same Sheet

    Thanks. I will do this for all future posts. Thanks for your patience.

  9. #9
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: VBA Code for Discreet 2 Way Link Between Cells on Same Sheet

    Just count a bit and find out how offset works

    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim solo41 As Range, solo61 As Range, solo157 As Range
      Set solo41 = Range("A41:A56")
      Set solo61 = solo41.Offset(20)
      Set solo157 = solo41.Offset(116)
      
      Application.EnableEvents = False
      If Not Intersect(Target, solo41) Is Nothing Then
        Target.Offset(20) = Target
        Target.Offset(116) = Target
      End If
      If Not Intersect(Target, solo61) Is Nothing Then
        Target.Offset(-20) = Target
        Target.Offset(96) = Target
      End If
      If Not Intersect(Target, solo157) Is Nothing Then
        Target.Offset(-116) = Target
        Target.Offset(-96) = Target
      End If
      Application.EnableEvents = True
    End Sub
    Messages have been translated from Dutch to English by means of google translate.

  10. #10
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    MS365 V.2406
    Posts
    2,307

    Re: VBA Code for Discreet 2 Way Link Between Cells on Same Sheet

    Possibly...
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rg1 As Range, rg2 As Range, rg3 As Range
    
        Set rg1 = Me.Range("a41:a56")
        Set rg2 = rg1.Offset(20, 0)
        Set rg3 = rg1.Offset(116, 0)
        
        Application.EnableEvents = False
        
        If Not Intersect(Target, rg1) Is Nothing Then
            Target.Offset(20, 0) = Target
            Target.Offset(116, 0) = Target
        End If
        
        If Not Intersect(Target, rg2) Is Nothing Then
            Target.Offset(-20, 0) = Target
            Target.Offset(96, 0) = Target
        End If
        
        If Not Intersect(Target, rg3) Is Nothing Then
            Target.Offset(-116, 0) = Target
            Target.Offset(-96, 0) = Target
        End If
        
        Application.EnableEvents = True
    End Sub

  11. #11
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: VBA Code for Discreet 2 Way Link Between Cells on Same Sheet

    @dangelor, What's the difference from # 8?

  12. #12
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    MS365 V.2406
    Posts
    2,307

    Re: VBA Code for Discreet 2 Way Link Between Cells on Same Sheet

    Nothing really. I just replied to post #5. Didn't see your post.

+ 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] Assistance with running 2 macros on 1 sheet. Code included! 2 buttons on sheet to link.
    By superCWdad in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2021, 03:08 PM
  2. how can i link this code to another sheet?
    By lee111 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-10-2018, 11:34 AM
  3. [SOLVED] VBA - Link in code to get data from one sheet paste in another sheet.
    By KSveigaard in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-24-2017, 11:44 AM
  4. Replies: 0
    Last Post: 01-28-2014, 02:45 PM
  5. Averaging discreet cells
    By Tonymullion in forum Excel General
    Replies: 6
    Last Post: 11-16-2011, 12:09 PM
  6. Function for discreet graph
    By hsvfan19 in forum Excel General
    Replies: 0
    Last Post: 03-28-2009, 05:46 AM
  7. VBA Code to link to external sheet
    By excelnovice83 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-20-2008, 03:02 PM

Tags for this Thread

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