PDA

View Full Version : Macro which auto runs when every excel workbook is opened



raghavarock
10-16-2016, 11:01 AM
Hi there,

I want a code that checks every excel file that is opened by user will have to check if the excel workbook has a specific sheet and if yes then check Cell value in B5 is "12345". If not then a msg box stating to let user know that the file is invalid.


Note the Sheet the code would be looking is actually hidden and workbook is protected.

appreciate you your help in this regard.

SamT
10-16-2016, 11:26 AM
Put this in the ThisWorkBook Module in every book that needs to be checked

Private Sub Workbook_Open()
On Error Resume Next
If Not Sheets("Specific Sheet Name Here").Range("B5") = "12345" Then
MsgBox "Invalid File, Please Jump in Lake."
End If
End Sub

mikerickson
10-16-2016, 03:59 PM
You could avoid all those copies of the same event code by putting this in the ThisWorkbook code module of your Personal Macro Workbook.


' in ThisWorkbook code module of Personal Macro Workbook

Public WithEvents ThisApp As Application

Private Sub Workbook_Open()
Set ThisApp = Me.Application
End Sub

Private Sub ThisApp_WorkbookOpen(ByVal Wb As Workbook)
On Error Resume Next
If Not Wb.Sheets("Specific Sheet Name Here").Range("B5") = "12345" Then
MsgBox "invalid"
End If
End Sub

raghavarock
10-16-2016, 11:03 PM
Hi mikerickson,


Thanks a lot for the above code , but I am having three observations

1) code is running even before the excel file is opening , i mean it is running when the application is opened and msg box showing up which is not we want :) .
2 ) with regard to MSG box i want it to be pop when code finds a sheet with name "Specific name" in the workbook that is opened then only it has to go to next criteria of Checking the cell value , if not simply exit the Sub.






[/COLOR]

mikerickson
10-17-2016, 06:19 AM
Substitute this:


Private Sub ThisApp_WorkbookOpen(ByVal Wb As Workbook)
Dim SheetInQuestion As Worksheet

On Error Resume Next
Set SheetInQuestion = Wb.WorkSheets("SpecificSheetName")
On Error Goto 0

If Not SheetInQuestion Is Nothing Then
If SheetInQuestion.Range("B5") <> "12345" Then
MsgBox Wb.Name & " is invalid."
End If
End If
End Sub

p45cal
10-19-2016, 02:39 AM
Read http://www.excelguru.ca/content.php?184
The cross posting rules here and at MrExcel, Chandoo and many others are all very similar.