+ Reply to Thread
Results 1 to 10 of 10

Macros that should automatically run when a cell value change ++

  1. #1
    Registered User
    Join Date
    01-11-2020
    Location
    Norway
    MS-Off Ver
    Microsoft Office 2019
    Posts
    23

    Macros that should automatically run when a cell value change ++

    Hi,

    I am quite new to VBA programming in excel, but I try to learn something new each day.

    So in general, I have created an excel sheet that basically solves an equation with the goalseek function. I did this when changing a value, so I used this code:
    _______________
    Please Login or Register  to view this content.
    ________

    So, this equation is solved automatically when I change a variable, which I can do from a cell in another worksheet. I then use the command ='Sheetname!'C6 <-- or something in that cell.

    So, there are some few things that I would love to know, and I spent 6 hours last sunday figuring out how I can not do them

    One of the things I would love to do is the following:
    I would like to be able to change the value of the cell (which makes the program run) from several other cells in a different worksheet. At the same time, I want excel to automatically copy several values from the same worksheet (the equation worksheet) and paste them in another worksheet each time the cell changes value.

    E.G:
    I have a column with different values from dropdown lists. The cells in the row needs to be limited to, let's say 20 cells that can affect the value of the cell that runs the code that solve the equation.
    Every time the code solves an equation, I would like to copy 6-7 values from the sheet that has the equation on it to the sheet that I changed the cell value (the active row in the sheet which I changed a cell in a drop down menu (this is the same sheet that has the cell column of 20 cells with drop down menus)).

    I the program to be able to do this for all the 20 cells. But I have no Idea how.

    Also, If I really want to learn VBA in excel, where should I start?


    If you see this thread for the first time just go to the bottom where I attached my excel sheet
    Last edited by -Citizen; 01-20-2020 at 06:09 PM. Reason: Editing the post topic to fullfil admins request

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,056

    Re: Macros that should automatically run when a cell value change ++

    It looks like what you need is a Worksheet Change Event.

    Please Login or Register  to view this content.
    The code evaluates to if the changed cell (target) is within the range, the intersection expression is false and the program continues. If the target is outside the range then the intersection is nothing and the program exits without executing any more code.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    01-11-2020
    Location
    Norway
    MS-Off Ver
    Microsoft Office 2019
    Posts
    23

    Re: Macros that should automatically run when a cell value change ++

    Thank you for that!

    Just a quick question, should I use this code in the sheet where I want my values transfered, or should I use it in the sheet where I have my equation (the equation sheet)?

    Also, is it possible to have several of these "autorun" codes in one sheet?

    I also want to know how you get these values in the column transfered to one cell in another sheet when it is active?
    Last edited by -Citizen; 01-14-2020 at 02:43 PM. Reason: Need more information:)

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,056

    Re: Macros that should automatically run when a cell value change ++

    Use this code on the sheet where the values are changed.

    You can have multiple "live' triggers for the code. By "live" I mean if you have Private Sub Worksheet_Change(ByVal Target As Range) the code is trying to run any time ANY cell on the sheet is changed.

    That's why the sample the If Intersect line. it jumps out of the code if what's being changed is not in the range specified.

    So the answer is, yes you can have multiple live triggers, but you have to figure out how to tell the code when to execute them. For example if you just want to look at four cells. Let's say B1, B3, B5 and B7, you can write code that looks like:

    Please Login or Register  to view this content.
    This is only one such scheme. You could also use logic such as
    Please Login or Register  to view this content.
    Without seeing the specifics of your worksheet, this is the best I can offer.

  5. #5
    Registered User
    Join Date
    01-11-2020
    Location
    Norway
    MS-Off Ver
    Microsoft Office 2019
    Posts
    23

    Re: Macros that should automatically run when a cell value change ++

    Thank you again for reply!

    I really hate to bother you so much, but I really appreciate your help.
    I have now entered the following code to remove one value from one sheet to another one.

    Please Login or Register  to view this content.
    So, when I run this code on any other worksheet than the one named "Calculations", it works. When trying to run it in the "Calculations" worksheet I get the following:

    Run-time error '9':
    Subscript out of range

    What is worth mentioning is that the worksheet I am trying to copy the values into has the following code in it:

    Please Login or Register  to view this content.
    Since this is the only worksheet I am not able to paste any values into, I am starting to believe that the code above is ruining everything. I still need the code to make the calculations though. Any ideas on what to do?
    Last edited by -Citizen; 01-19-2020 at 07:06 AM.

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,056

    Re: Macros that should automatically run when a cell value change ++

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  7. #7
    Registered User
    Join Date
    01-11-2020
    Location
    Norway
    MS-Off Ver
    Microsoft Office 2019
    Posts
    23

    Re: Macros that should automatically run when a cell value change ++

    Hi,

    I have now attached an excel sheet that demonstrates my problem.

    Basically I want to change the values in the "D" column in sheet 2 (Ark 2) in Norwegian, the values should transfer to C4 in sheet 1 (Ark1) and then the goalseek function should run automatically.

    The sheet should be attached.

    This is not the sheet that I am working on, but the exact same problem. The sheet I am working on is too big and too complex to start looking into (not complex in vba programming, but everything else).

    Thank you for any assistance!


    If you are able to solve this one, please also show me how to transfer values from sheet 1 (Ark1) to sheet 2 (Ark2) automatically after the equation is solved and paste them to the right of the row where you changed the value (in sheet 2 (Ark2)).
    Attached Files Attached Files
    Last edited by -Citizen; 01-20-2020 at 06:10 PM.

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,056

    Re: Macros that should automatically run when a cell value change ++

    I'm a bit confused as to what your remaining question is.

    On Ark2 you mention you want to copy the range D2 to D8 to cell C4 on Ark1 when Cell C4 on Ark1 is changed. Is this correct?

    I'll have to read up on goal seek. I generally use solver.

  9. #9
    Registered User
    Join Date
    01-11-2020
    Location
    Norway
    MS-Off Ver
    Microsoft Office 2019
    Posts
    23

    Re: Macros that should automatically run when a cell value change ++

    Hi,

    Thanks again for paying attention to this.

    Please see the newest sheet attached!

    Now almost everything is solved. If I change a value in the yellow column in "Ark2", it will transfer to position C4 in "Ark1" which again will automatically activate the Goalseek funcion and solve the equation.

    Now there is one more thing to do...
    After the equation is solved I also want to copy the values which are in "Ark1" Range("B4:D4,G4") to "Ark2".

    I think one of the main problem with this is that the goalseek command use 1 second or so to calculate (especially on more challenging tasks), so it needs to be like 3 seconds delay before the values gets copied to "Ark2".

    Do you know how to do this?

    It is important that you use the newest spreadsheet attached
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-11-2020
    Location
    Norway
    MS-Off Ver
    Microsoft Office 2019
    Posts
    23

    Re: Macros that should automatically run when a cell value change ++

    I would also add that it would be really awesome if all of these commands could have been done by pressing only one button (a button that you create where all the macros are stored).

+ 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. Synchronize multiple users one particular excel work sheet in google sheet?
    By bala04msw in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-07-2018, 04:22 AM
  2. Replies: 3
    Last Post: 11-29-2015, 09:33 PM
  3. [SOLVED] Formula in one work sheet wont accept result from another in a different work sheet.
    By Belinea2010 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-07-2015, 12:24 PM
  4. [SOLVED] copy work sheet from another work book into already exits sheet
    By Baskar5353 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-07-2015, 03:18 AM
  5. Replies: 0
    Last Post: 07-16-2013, 06:19 AM
  6. macro for copying one sheet to a new separate excel work sheet
    By Shweta.Kapdekar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-08-2012, 02:44 AM
  7. Replies: 1
    Last Post: 10-29-2005, 11:05 AM

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