Change Cell in Different Workbook?

[whahmira]

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

[kasan]

Hi,
In case you have both files opened, this code will change value in another workbook:

Please Login or Register  to view this content.

[whahmira]

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

[kasan]

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.

[whahmira]

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.

[Vraag en antwoord]

Can you place the files without sensitive information?

[kasan]

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.

[whahmira]

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

[kasan]

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

[whahmira]

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!

[whahmira]

1.PNG

Here is an image of what I have just explained

[Vraag en antwoord]

Nice picture.

[whahmira]

Any ideas?

[Marc L]

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

Yes, the picture is nice !

[whahmira]

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.

[Vraag en antwoord]

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?

[whahmira]

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