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]

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.

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

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]

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]

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]

Hello - can you show me the entire code you have on "Thisworkbook" module

 

[mhastings]

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]

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

Thanks,

Matt

 

[VBA Geek]

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]

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]

 Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Me.Saved = True


End Sub

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