auto-run a macro for any worksheet

[Mihael]

Hi, I would like to auto-run a macro when Excel is opened and cell C4 contains the word "pallet". I have tried to put this macro in the PERSONAL.XLSB directory and then in "This workbook", however the Macro starts before the spreadsheet is opened.. I did this with a Private Sub Workbook_Open().

Does anyone know how to run a macro when a sheet is opened with a value in C4?

 

[Stiuart_W]

You need to clarify when you want run this macro. Firstly you say when the Workbook is opened and then later you say when "sheet is opened". That doesn't make sense as Worksheets aren't opened.

You can have a macro in "Workbook" module and that macro can run on a number of different criteria - a number of which relate to Worksheet activity - that macro can then check the value of C4 in a Worksheet and then continue or exit the macro as appropriate

 

[Mihael]

The reason why I wrote "This workbook" is that in the VBAproject directory I somewhere need to put my macro. I chose the Personal directory, because that is used for all Excel files. In this folder I only have "ThisWorkbook" so I have put it in there. If I put the macro in the folder in an Excel file, it only activates the macro when this certain file is opened. I need this macro to run every time whenever Excel opens a workbook and there is certain data in a cell.

 

[FormR]

I need this macro to run every time whenever Excel opens a workbook and there is certain data in a cell.

Hi, you can try this in the "ThisWorkBook" module of your personal macro workbook.

Make sure that you fully qualify any ranges that you intend to use from the newly opened workbook with the wb variable.

Private WithEvents App As Application
Private Sub Workbook_Open()
    Set App = Application
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
If LCase(Wb.ActiveSheet.Range("C4").Value) = "pallet" Then
    MsgBox "Do Stuff!"
End If
End Sub

 

[Mihael]

Exactly what I needed. Thanks a lot!

 

[Mihael]

Hi,

I found a small problem with the Macro.. I think this macro checks only once if the value is correct in the If statement. In my case Excel is opened, then a converter exports the data in this sheet, and then I need it to check the if statement.

Is it possible that Excel checks the if statement a whole minute?

The code I have is:

Private WithEvents App As ApplicationPrivate Sub Workbook_Open()
    Set App = Application
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
If LCase(Wb.ActiveSheet.Range("C4").Value) = "frontside" Then