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

mhastings

New Member
Joined
Dec 11, 2014
Messages
9
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

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Hello, you can do this by using class module approach.

Add a new class module to your Test workbook project ans call it cAppEvents. Paste the below code in it.

Code:
Option Explicit


Private WithEvents EXCEL_APP As Excel.Application


' **********************************************************************************************
Private ErrorMessage As String ' error message returned while trying to edit range format
Private sWorkbookThatOpens As String ' name of the workbook that if opened triggers the event
Private sWorkBookToEdit As String ' name of the workbook whose range will be modified
Private sSheetToEdit As String ' name of the sheet whose range will be modified
Private sCellsToEdit As String ' name of the range that will be modified


' *********** SETTINGS for Editing the Range
Private varxAppInteriorColor As Variant
Private varxAppFontColor As Variant
Private varxAppTurnBold As Variant
Private varxAppFontName As Variant
Private varxAppFontSize As Variant


Private Sub Class_Initialize()
    Set EXCEL_APP = Application
End Sub


Private Sub EXCEL_APP_WorkbookOpen(ByVal WB As Workbook)
    If StrComp(WB.Name, sWorkbookThatOpens, vbTextCompare) = 0 Then
        If Not EditTargetCells Then MsgBox ErrorMessage
    End If
End Sub


Private Function EditTargetCells() As Boolean
                            
Dim WB As Excel.Workbook, WS As Excel.Worksheet, rng As Excel.Range


EditTargetCells = False ' assume failure


On Error Resume Next
Err.Clear
Set WB = EXCEL_APP.Workbooks(sWorkBookToEdit) ' find workbook


' exit with error if WB doesn't exist
If WB Is Nothing Then ErrorMessage = "Workbook with name: " & sWorkBookToEdit & " doesn't exist.": Exit Function


' check for sheet existance
Set WS = WB.Worksheets(sSheetToEdit)
If WS Is Nothing Then ErrorMessage = "Worksheet named: " & sSheetToEdit & " doesn't exist on workbook: " & sWorkBookToEdit: Exit Function


' check for range existance
Set rng = WS.Range(sCellsToEdit)
If rng Is Nothing Then ErrorMessage = "The Range named: " & sCellsToEdit & " doesn't exist."




' got here, can edit range
On Error GoTo Err_Handler
If Not IsEmpty(varxAppInteriorColor) Then rng.Interior.Color = varxAppInteriorColor
If Not IsEmpty(varxAppFontColor) Then rng.Font.Color = varxAppFontColor
If Not IsEmpty(varxAppTurnBold) Then rng.Font.Bold = varxAppTurnBold
If Not IsEmpty(varxAppFontName) Then rng.Font.Name = varxAppFontName
If Not IsEmpty(varxAppFontSize) Then rng.Font.Size = varxAppFontSize


EditTargetCells = True


Main_EXIT:
Exit Function


Err_Handler:
    ErrorMessage = Err.Description
    Resume Main_EXIT
End Function




Property Get ErrMessage() As String ' READ ONLY Property
    ErrMessage = ErrorMessage
End Property




' --------------------------- LET Properties
Property Let WorkbookThatOpens(S As String) ' name of the workbook that opens
    sWorkbookThatOpens = S
End Property


Property Let WorkbookToEdit(S As String) ' name of the workbook to edit
    sWorkBookToEdit = S
End Property


Property Let SheetToEdit(S As String) ' name of the sheet to edit
    sSheetToEdit = S
End Property


Property Let CellsToEdit(S As String) ' name of the cells or named Range to edit
    sCellsToEdit = S
End Property


Property Let xAppInteriorColor(V As Variant) ' pass long Value representing interior color
    varxAppInteriorColor = V
End Property


Property Let xAppFontColor(V As Variant) ' pass long long value representing font color
    varxAppFontColor = V
End Property


Property Let xAppTurnBold(V As Variant) ' turn the cell(s) bold setting
    varxAppTurnBold = V
End Property


Property Let xAppFontName(V As Variant) ' name of the font
    varxAppFontName = V
End Property


Property Let xAppFontSize(V As Variant) ' size of the font
    varxAppFontSize = V
End Property






'  ----------------------GET Properties
Property Get WorkbookThatOpens() As String
    WorkbookThatOpens = sWorkbookThatOpens
End Property


Property Get WorkbookToEdit() As String
    WorkbookToEdit = sWorkBookToEdit
End Property


Property Get SheetToEdit() As String
    SheetToEdit = sSheetToEdit
End Property


Property Get CellsToEdit() As String
    CellsToEdit = sCellsToEdit
End Property


Property Get xAppInteriorColor() As Variant  ' pass long Value representing interior color
    xAppInteriorColor = varxAppInteriorColor
End Property


Property Get xAppFontColor() As Variant  ' pass long long value representing font color
    xAppFontColor = varxAppFontColor
End Property




Property Get xAppTurnBold() As Variant  ' turn the cell(s) bold setting
    xAppTurnBold = varxAppTurnBold
End Property


Property Get xAppFontName() As Variant ' name of the font
    xAppFontName = varxAppFontName
End Property


Property Get xAppFontSize() As Variant ' size of the font
    xAppFontSize = varxAppFontSize
End Property


now, in the workbook_Open event of your Test workbook add the below code

as you will see there're several setting which you can edit, you can determine the name of the workbook that when opened triggers the event, the name of the workbook on which the change takes place, the name of the worksheet on which the change takes place and the range name on which the change takes place.

in addition you can pass to it your settings, ie interiorColor, FontName etc. More format settings can be added if necessary, I only added a few.

I did a fast testing and it seems alright, let me know if any bug

Code:
Private clsEvents As cAppEvents


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set clsEvents = Nothing
    Cancel = False
End Sub


Private Sub Workbook_Open()
    Set clsEvents = New cAppEvents
    With clsEvents
        .CellsToEdit = "A1" ' cell(s) to edit
        .SheetToEdit = "Sheet1" ' in which worksheet
        .WorkbookToEdit = "Tables.xlsm" ' in which workbook
        .WorkbookThatOpens = "Master.xlsx" ' if which workbook opens
        
        ' settings, don't pass anything if you wish not to change that setting
        .xAppInteriorColor = vbYellow
        .xAppFontName = "Calibri"
        .xAppFontSize = 10
        .xAppTurnBold = False
        .xAppFontColor = vbBlack
    End With
End Sub
 

mhastings

New Member
Joined
Dec 11, 2014
Messages
9
Hey thank you so much for the VBA and the quick reply. I will work this in and get back to you on it if I am missing anything. Much appreciated!
 

mhastings

New Member
Joined
Dec 11, 2014
Messages
9
Hello again,

I think I am doing something wrong on the sheet, I renamed the class module to cAppEvents as shown and pasted the first code into it, the second code I think is where I am losing it. Also for the lower code, I pasted that into "ThisWorkbook" and when I open the document Tables.xlsm, I get a compile error under the workbook_open() event and it highlights the section .CellsToEdit. Again, the help is much appreciated.

Best regards,

Matt
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Hello - can you show me the entire code you have on "Thisworkbook" module
 

mhastings

New Member
Joined
Dec 11, 2014
Messages
9
So far, the only code I have in this workbook is this code:

Code:
Private clsEvents As cAppEvents




Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set clsEvents = Nothing
Cancel = False
End Sub




Private Sub Workbook_Open()
Set clsEvents = New cAppEvents
With clsEvents
.CellsToEdit = "B4" ' cell(s) to edit
.SheetToEdit = "Sheet1" ' in which worksheet
.WorkbookToEdit = "Tables.xlsm" ' in which workbook
.WorkbookThatOpens = "Master.xlsm" ' if which workbook opens

' settings, don't pass anything if you wish not to change that setting
.xAppInteriorColor = vbYellow
.xAppFontName = "Calibri"
.xAppFontSize = 10
.xAppTurnBold = False
.xAppFontColor = vbBlack
End With
End Sub

I temporarily removed the other VBA that is used to reset the box colors until I have this code figured out. This code was placed in ThisWorkbook under workbook - Tables.xlsm. Sorry for the delay in reply.
 

mhastings

New Member
Joined
Dec 11, 2014
Messages
9
Also, how do you past code into the clean boxes as shown in your post when posting a reply?

Thanks,

Matt
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
hello

at first glance the code seems fine, what is the error that you get?



Also, how do you past code into the clean boxes as shown in your post when posting a reply?

Thanks,

Matt
you 've to wrap the code around(replace 0 with O) [C0DE]code here[/C0DE]
 

mhastings

New Member
Joined
Dec 11, 2014
Messages
9
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.
 

mhastings

New Member
Joined
Dec 11, 2014
Messages
9
Code:
 Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Me.Saved = True


End Sub
Disregard this particular post, Im just testing the box feature.
 

Forum statistics

Threads
1,081,992
Messages
5,362,601
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top