Macro which auto runs when every excel workbook is opened

[raghavarock]

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]

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]

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]

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]

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]

Read http://www.excelguru.ca/content.php?184
The cross posting rules here and at MrExcel, Chandoo and many others are all very similar.