VBA assistance request - trigger event on one workbook based on another workbook opening

[mhastings]

Hello,

I am trying to come up with a macro that will make a change to one workbook based on another workbook opening. basically something to the extent of this:

If workbook 'Master' opens, then change the color of cell A1 on 'sheet1' in workbook 'Tables'.

The scope of this project is as follows: I work in a lab where myself and other users are not always directly in front equipment to see it shut off. The idea is that when one of my tests shut off, the computer will open an assigned excel file (this part here is assigned through our control system settings), this excel file is set to close after 1 second of being open, meanwhile this sheet being opened triggers another excel file to change a cell color of a specific cell indicating a fault or that a test has stopped. I have some of the sheet constructed / necessary macros for some functionality such as the timer macros to close the document on its own and another to reset the faults, but I am having trouble developing an event module to make a change to document A based on document B being briefly opened. I am more then open to other ideas on how to accomplish the same general goal. Thoughts?

Best regards,

Matt

 

[VBA Geek]

When I open workbook - Table.xlsm it says "Compile error: Method or data member not found", once I click OK, in the VB menu under ThisWorkbook it highlights Private Sub Workbook_Open() in yellow, and .CellsToEdit in blue.

Hello - in my very first post, are you sure that you've copied the first block of code in its entirety? I think you might be missing parts of code at the bottom

 

[mhastings]

Ha, it would appear that I missed part of the code for the Class Module. Thanks so much for the help! I have an additional question, now that my error has been resolved. As planned it changes the color of cell A1 (in reality, A1 is just for testing purposes, if all goes as planned, their will actually be more like 10 documents, 1 for each channel that trigger an event in respective cells on the Tables sheet) The only issue I have now is network communication, I placed both workbooks on a network drive, and if both are opened from the same PC then it will trigger the event, however if Tables is opened from 1 PC and Master is opened from a second PC it does not trigger the even on the 1st PC. Is this possible to achieve?

 

[VBA Geek]

The only issue I have now is network communication, I placed both workbooks on a network drive, and if both are opened from the same PC then it will trigger the event, however if Tables is opened from 1 PC and Master is opened from a second PC it does not trigger the even on the 1st PC. Is this possible to achieve?

Can you test with your colleague if the event if triggered if the workbooks are being opened from different PCs?
I can't test this atm

 

[mhastings]

Last night I placed files Master and Tables onto a network drive, I left tables open on my PC and opened Master on a colleague's PC, but I could only trigger the event if opening both documents from the same PC.