auto-run a macro for any worksheet

Mihael

Board Regular
Joined
Mar 18, 2016
Messages
169
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?
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Stiuart_W

Well-known Member
Joined
Jul 3, 2013
Messages
516
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

Board Regular
Joined
Mar 18, 2016
Messages
169
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,467
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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

Board Regular
Joined
Mar 18, 2016
Messages
169
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,086
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top