You could put the code in a different workbook that you open after you open the
other workbooks.
Then the code could loop through all the open workbooks and all the worksheets
in each workbook looking for that value.
But if you want to keep the code in your personal.xls, you could use an
application event that looks for a newly opened workbook and checks each
worksheet in that newly opened workbook.
This does no validation (like checking for locked cells on protected worksheets)
and doesn't avoid any other events.
Option Explicit
Private WithEvents XLApp As Excel.Application
Private Sub Workbook_Open()
Set XLApp = Excel.Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub XLApp_WorkbookOpen(ByVal Wb As Workbook)
Dim wks As Worksheet
For Each wks In Wb.Worksheets
If LCase(wks.Range("a1").Value) = LCase("it's here!") Then
wks.Range("a1").Value = "Not any more!"
End If
Next wks
End Sub
This goes in the ThisWorkbook module.
Personally, I wouldn't put anything like this in my personal.xls workbook. I'd
put it in a dedicated workbook/addin. Then load that workbook when I knew I
wanted it.
In fact, I like the shortcut key approach!
(E-Mail Removed) wrote:
>
> I have a hidden Excel workbook with code that I put in the XLSTART
> folder. When I start up Excel that file opens in the background. I
> open up another Excel file that I want the code in the hidden workbook
> to run. I then press my macro shortcut keys ("Ctrl-Shift-A" or
> whatever) to run the code.
>
> Is it possible to make the hidden workbook check a cell value in each
> workbook when the workbook first opens? This way I can look for a
> certain value and run the code automatically without pressing any
> macro shortcut keys. I cannot put code in the open Excel files
> because they are system generated. The code in the hidden workbook is
> to format these system generated files. Thanks...
--
Dave Peterson
Similar Threads
