+ Reply to Thread
Results 1 to 9 of 9

Same WorkSheet Change Event Code Across Multiple Sheets Help

  1. #1
    Registered User
    Join Date
    06-26-2014
    Location
    Seattle
    MS-Off Ver
    2013 Professional
    Posts
    6

    Same WorkSheet Change Event Code Across Multiple Sheets Help

    Long time reader, first time poster...

    Using Excel 2013 Professional on a Windows PC.

    I have an Excel file with multiple sheets - each sheet is designed for individuals to make their own unique entries across a row and each row represents a complete entry. All the sheets in the workbook have identical columns.

    I'm trying to create a Master Worksheet within the same file that captures entries from each of the other sheets as a live worksheet change event --- so when someone adds an entry to their individual sheet it adds the cell addresses for that row to the Master Sheet, and if they delete it, the row in the Master Sheet is cleared.

    I have a WorkSheet change event code that does exactly that, the problem is that it only works for one sheet at a time. Using the same code in a different person's sheet only adds/clears the cell addresses to/from the Master Sheet if the entry is in a row that isn't the same as any other sheet (likely due to the "intersect" coding).

    Is there a way to achieve this either using a variation of this current code or is there another better way to solve this.

    File is attached and here's the code:

    - The Master Sheet is called the "Dashboard" (Sheet3)
    - "Rep sheet" is the individual person's sheet (ActiveSheet)
    - Column "C" in each worksheet is the trigger for adding/subtracting a new entry

    Please Login or Register  to view this content.
    Any help is greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Same WorkSheet Change Event Code Across Multiple Sheets Help

    try using Workbook_SheetChange in thisworkbook module
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    06-12-2014
    Posts
    42

    Re: Same WorkSheet Change Event Code Across Multiple Sheets Help

    Jaylove, you can probably get to the bottom of your issues if you added a "Stop" to your worksheet change event and use keyboard F8 to step through your code to check the values of your variables while your code executes.

    That said, I'd approach this differently based on the following suggestions:

    1) Rather than work with cell addresses, I'd capture user changes to a record by copying the entire row's values into an array variable and then pasting those values (after necessary checks) as a full row to the Dashboard. Your current method will result in multiple links between the Dashboard and other sheets causing a slow response time. Data integrity should be maintained by conducting appropriate checks before adding to the Dashboard, rather than by having "live links"

    2) It seems like you're going to ultimately add the same code under several worksheets. It would be better to write a single function and point all the worksheet change events to that function so you only have to update one place rather than copy the entire code over several sheets each time something changes

    3) I think you only intend your code to run when column C changes but it currently will get executed whenever any cell in any row / column gets changed

    Hope this helps!

  4. #4
    Registered User
    Join Date
    06-26-2014
    Location
    Seattle
    MS-Off Ver
    2013 Professional
    Posts
    6

    Re: Same WorkSheet Change Event Code Across Multiple Sheets Help

    Aks2014 - thank you for your quick response.

    I agree - I'd rather not bog down the file with a bunch of live links. I'd rather go with your suggestion of one function that executes only as updates are actually needed sans using cell address.

    I'm not familiar with writing functions. It seems like someone else must have had a need to accomplish what I'm trying to do at some point. Can you point me to a forum or guide to help me write this function?

    Thanks again,
    - Jay

  5. #5
    Registered User
    Join Date
    06-26-2014
    Location
    Seattle
    MS-Off Ver
    2013 Professional
    Posts
    6

    Re: Same WorkSheet Change Event Code Across Multiple Sheets Help

    patel45,

    I just tried using a WorkBook_SheetChange in "This Workbook" (i.e. "Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)" with the same code and got the same result. Would have been great if that did it...

  6. #6
    Registered User
    Join Date
    06-12-2014
    Posts
    42

    Re: Same WorkSheet Change Event Code Across Multiple Sheets Help

    So on each Worksheet change event, you could have something like:

    Please Login or Register  to view this content.
    That way, every time there is a change on the spreadsheet, the function JayFunction is triggered and you are passing the "Target" to JayFunction along with any additional values you might want to pass as needed within JayFunction and separated by commas. JayFunction (as shown below) can be placed in any module of the workbook

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-26-2014
    Location
    Seattle
    MS-Off Ver
    2013 Professional
    Posts
    6

    Re: Same WorkSheet Change Event Code Across Multiple Sheets Help

    Thank you, Aks2014.

    Just tried your suggestion. I just threw in the original code as written for the function in the module. It looks as though the the function is being triggered when data is entered into the "C" column of the sub-sheets but nothing is being written to the Master Sheet. I may need to play with it a bit more and do some more research on writing Function code...

  8. #8
    Registered User
    Join Date
    06-26-2014
    Location
    Seattle
    MS-Off Ver
    2013 Professional
    Posts
    6

    Re: Same WorkSheet Change Event Code Across Multiple Sheets Help

    I just tried this code within the sub sheets and it seems to work:

    Please Login or Register  to view this content.
    I'd rather find a function that effects the change of all sub sheets without having to copy this WorkSheet_Change code within each sub sheet, which I'm afraid may end up getting jinky.

    Still, I may be able to get this code to do what I need...

  9. #9
    Registered User
    Join Date
    06-26-2014
    Location
    Seattle
    MS-Off Ver
    2013 Professional
    Posts
    6

    Re: Same WorkSheet Change Event Code Across Multiple Sheets Help

    I've gone back to my original Worksheet_Change event code, but using it in "This Workbook" and that seems to work. But instead of copying cells addresses over to the Master Sheet I want to copy values only.

    My issue now is making sure only new or revised cells in a unique entry (i.e. row within the other sub sheets) are added, changed or cleared within the Master Sheet.

    The way I'm attempting to do it now is by assigning a unique identifier to the end of each row that contains an entry (e.g. if data is entered in column C on a sub sheet, column Z of that row will now have an ID - "Sheet1$C$7" - which will then be copied over to the Master Sheet with the rest of the row). Then I can use that ID to find the specific entry on the Master Sheet whenever a change is made to the entry on the sub sheet.

    Is this the best way to go about this or is there a simpler more elegant approach?

+ 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. Worksheet change event code
    By Nitefox in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-18-2014, 10:52 PM
  2. Code for Worksheet Change Event Needed
    By ARGK in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-17-2013, 11:54 PM
  3. Worksheet Change Event code help
    By kev_33 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-12-2013, 01:51 AM
  4. Change Event Multiple Sheets
    By ckirkwalsh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2011, 01:46 PM
  5. Replies: 5
    Last Post: 06-23-2005, 06:05 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