+ Reply to Thread
Results 1 to 8 of 8

Mulitple processes within Sub Worksheet_Change

  1. #1
    Registered User
    Join Date
    11-21-2019
    Location
    Salem, OR
    MS-Off Ver
    Excel for Office 365
    Posts
    16

    Mulitple processes within Sub Worksheet_Change

    I have two macros that work on their own separately, but when placed together on a worksheet module, I get errors or one of them stops working. The first macro clears dependent drop-down menus when the parent drop-down is changed. The second macro copies down formulas as new records are entered into the spreadsheet.

    Vichopalacios was kind enough to provide the copy down formula for me in another post.

    I've tried to combine the macros in several ways and the best result was not getting an error notice, but the clearing of the dependent drop-down stopped working.

    I most likely missing a simple syntax line of code.


    Please Login or Register  to view this content.
    Last edited by elabamnoj; 01-22-2020 at 06:39 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Mulitple processes within Sub Worksheet_Change

    Maybe like this. Instead of copy/paste, we'll just transfer the formulas from the first data (row 2) to the current row when column A is edited. This eliminates copy/paste and eliminates the need to track the last row.
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-21-2019
    Location
    Salem, OR
    MS-Off Ver
    Excel for Office 365
    Posts
    16

    Re: Mulitple processes within Sub Worksheet_Change

    JBeaucaire,

    Thank you for your help, but unfortunately it didn't quite work. When it transferred the formula down from the top data row, the formula didn't adjust to the corresponding new row. Also, the dependent drop-down in column "I" didn't clear.

    I made a few adjustments to my original code above and everything seems to work fine. No error dialogue windows come up. The one thing I cannot make happen is the dependent drop-down cell to clear when the primary one is changed. If that does not clear, the formulas that are copied and filled down will reference the wrong lookups I've setup on a separate worksheet.

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,467

    Re: Mulitple processes within Sub Worksheet_Change

    I don't believe you can have 2 or more Worksheet_SelectionChange event handlers in the same worksheet class module. It's also unclear why you'd want the 2nd rather than putting its only statement into the first so-named event handler and moving lRow inside that procedure.

    Next, tangent: if it made sense for lRow to be external to any procedure, it may be better to declare it as Private rather than Public. Private for declarations outside procedures means available to all procedures within that module, but not accessible in other modules. I can't imagine you'd want to access lRow from code in other modules.

    Why not

    Please Login or Register  to view this content.
    ?

    Note: if the last nonblank cell in col A were A1234, then if you select A1235, this event handler will put formulas into T1235, Y1235, Z1235, etc. If you don't enter anything in cell A1235, then select, say, C1220, then select A1235 again, the event handler will happily reenter the same formula again in all the indicated columns in row 1235. Do you want to enter those formulas only when all cells in the indicated columns in row 1235 are blank?

    ADDED: continuation from above, A1234 the bottommost nonblank cell in col A, if you select A1236, nothing happens. Would you want formulas copied into rows 1235 and 1236 instead?
    Last edited by hrlngrv; 01-22-2020 at 11:14 PM.

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Mulitple processes within Sub Worksheet_Change

    My original suggestion back to using your clipboard approach so the formulas update.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-21-2019
    Location
    Salem, OR
    MS-Off Ver
    Excel for Office 365
    Posts
    16

    Re: Mulitple processes within Sub Worksheet_Change

    JBeaucaire, hrlngrv,

    Thank you both for your input, unfortunately your solutions did not quite work the way I would have liked. The resetting of the dependent drop-down is my primary concern. Adding other event handlers keeps breaking the drop-down reset. When I have more time I will revisit this particular problem, but for the moment I will have to just settle with manually filling down the formulas at the end of the day when I review the data input.

    Cheers!

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Mulitple processes within Sub Worksheet_Change

    If you'd like to post your worksheet with sensitive data removed and the macro in its current state installed, I'll take one last crack at this.

  8. #8
    Registered User
    Join Date
    11-21-2019
    Location
    Salem, OR
    MS-Off Ver
    Excel for Office 365
    Posts
    16

    Re: Mulitple processes within Sub Worksheet_Change

    JBeaucaire,

    Thank you for your offer. I tried cleaning the data, but exporting the sheet kept breaking things. I inherited the workbooks from someone else, so have been slowly trying to trace all the dependencies and links. What I have now will suit my purposes. I've got other pressing projects to work on and will revisit this when another time.

+ 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. Reconciliation processes
    By Tpba in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-14-2018, 05:17 AM
  2. Process to run multiple processes.
    By sungen99 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 10-30-2015, 02:00 PM
  3. excessive occupation of macro processes
    By vientito in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-05-2014, 08:42 AM
  4. sumifs with mulitple columns and mulitple criteria in each column
    By bkaufman in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-18-2012, 05:11 PM
  5. Macro that processes subfolders
    By jimbofoxman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2009, 02:36 PM
  6. How many excel processes can you open?
    By Stanley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2006, 07:25 AM
  7. Display System Processes
    By Ashley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-21-2005, 11:30 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