Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cell value on workbook open
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cell value on workbook open
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! 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cell value on workbook open
Wow! Thanks, Dave. Works great! I was a little worried this
wouldn't be possible but turns out to be pretty simple. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA to check is someone else has a workbook open | Excel Discussion (Misc queries) | |||
If Then to check if a workbook is open | Excel Discussion (Misc queries) | |||
Check if workbook open | Excel Discussion (Misc queries) | |||
How check if workbook open? | Excel Programming | |||
Check for open workbook | Excel Programming |