+ Reply to Thread
Results 1 to 17 of 17

Change Cell in Different Workbook?

  1. #1
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Smile Change Cell in Different Workbook?

    Hello,

    I am trying to create a button inside of Workbook A sheet 1 that changes cell A1 in Workbook B sheet 1 from "Inquired" to "Ordered".


    I do not know VBA very well...any help would be greatly appreciated

  2. #2
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: Change Cell in Different Workbook?

    Hi,
    In case you have both files opened, this code will change value in another workbook:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Re: Change Cell in Different Workbook?

    Thanks for the help!
    Is there a way to do it if the excel workbook B is closed? Maybe an open/save/close function? Just not familiar with the software

  4. #4
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: Change Cell in Different Workbook?

    Try this code, it does open second file in a background, basically user does not see this process.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Re: Change Cell in Different Workbook?

    Thank you!

    Do you know if it is possible to find a cell that needs to be changed?

    Basically, if Workbook B's name (call it Order1) was listed in the A column in a list of other orders, and in B column was the value that needed to be changed to "Ordered", is there a function that can be used in macro to first find the appropriate row (Order1) in column A and then change the value in the column B of the same row?

    Sorry if that is confusing.

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

    Re: Change Cell in Different Workbook?

    Can you place the files without sensitive information?

  7. #7
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: Change Cell in Different Workbook?

    Something like this, code try to find "Order1" text in Column A of BookA. In case it was found - change value in column B.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Re: Change Cell in Different Workbook?

    That will definitely work. The only thing is that "Order1" is always changing depending on which file is opened. I am editing a template, so it cannot search for "Order1" it needs to search for "name of file" if possible

  9. #9
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: Change Cell in Different Workbook?

    What do you mean by "name of file"? Do you need to search this text: "name of file"? Or you need to search the name of file which is opened?
    Anyway now the text which should be found is hardcoded, its static. File to be opened are hardcoded too. If there are many files that should be opened and different text should be searched in each of them - we need to make some changes in the code to get this functionality.
    Could you describe full logic of your need? Other wise we just add some piece of code after your requirements become more clear

  10. #10
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Re: Change Cell in Different Workbook?

    Hi again!

    So here is my problem in as much detail as I can get.
    In Workbook A Sheet 1, I have a button set up to create a new order and a list of the orders created by the button. When the button is clicked, a new line appears at the end of the list containing the status of the order ("Inquired") and the new OrderNo. The OrderNo. is provided in a hyperlink that links directly to the new Workbook created (Workbook B). Inside of the Order (Workbook B Sheet 1) there is a button that can edit the list of orders in Workbook A Sheet 1. It is intended to launch Workbook A Sheet 1 and edit the specific cell related to the irow that the order in question is in from "Inquired" to "Ordered". Because the list of orders in Workbook A Sheet 1 is constantly changing length depending on how many orders are in the system, I am looking to see if there is a command that is not number specific, just column specific. I’d like something that looks in the first column for the OrderNo, when it finds the OrderNo. (whichever row that may be in) it knows to go over to the F column and rewrite the cell to “Ordered”. I need it to be a generic code because I am creating a template. So it cannot search for the specific OrderNo, it can only search for something generic. I should mention the OrderNo of each file that needs to be searched for in column A is also the name of the file that will be changing the code. So, for example, if OrderNo 520 was ordered, the file would be named "520.xlsm" and the macro could search for 'FileName' if possible.
    Sorry if this is confusing!

  11. #11
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Re: Change Cell in Different Workbook?

    1.PNG

    Here is an image of what I have just explained

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

    Re: Change Cell in Different Workbook?

    Nice picture.

  13. #13
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Re: Change Cell in Different Workbook?

    Any ideas?

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

    Re: Change Cell in Different Workbook?


    Activate the Macro Recorder and operate manually : you will get your own code base …

    Yes, the picture is nice !

  15. #15
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Re: Change Cell in Different Workbook?

    I have tried using the recorder and it does not work because it does not understand that my file is a template and that it needs to be generic for all types of files.

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

    Re: Change Cell in Different Workbook?

    Do you want to be helped? Two requests to attach the files and you can only say something that does not work. Where is the code of the recorded macro?

  17. #17
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Re: Change Cell in Different Workbook?

    Solved on my own.

    Used find command over range of A column to find (What:=OrderNo) and the search order as (SearchOrder:=xlByRows
    Then had it offset to the column I needed to change and changed the value in the cell to "ordered"
    then did active workbook save, active workbook close

+ 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] Change workbook name according cell value
    By HaroonSid in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-19-2017, 05:53 AM
  2. change color of activat cell in any workbook
    By sterio in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-15-2017, 02:10 PM
  3. Change cell fill color for all cells in a workbook based on a cell value
    By HDeuce in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-09-2017, 06:01 PM
  4. [SOLVED] Change cell value when workbook opens
    By juriemagic in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-18-2014, 02:06 AM
  5. Select a Cell but not change workbook
    By ScabbyDog in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-19-2012, 01:03 PM
  6. change a function in a workbook without changing every cell
    By sir lancelot in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-10-2005, 08:06 PM
  7. [SOLVED] Change workbook sheet reference using cell A1 to change a vairable
    By Reed in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-20-2005, 04:06 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