Auto run Addin code

[Soniya]

Hi All,

Is there a way that an Addin run automatically whenever a workbook is
opened?

for eg. if a user open a workbook containg sheet"ABC" then all formats
in that workbook to be deleted.

TIA
Soniya

 

[Bob Phillips]

Soniya,

You could do it with application events in the workbook.

As a starter, here is some code to check if a workbook contains a sheet
called "ABC"

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Dim sh As Worksheet
On Error Resume Next
Set sh = Wb.Worksheets("ABC")
On Error GoTo 0
If Not sh Is Nothing Then
'do your stuff
End If
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

 

[Soniya]

Is this can be saved as an Addin?

and it may work on opening any workbook?

When I test i get an error

Thanks

 

[Bob Phillips]

Soniya,

It should go in the ThisWorkbook code module of the add-in as I mentioned,
and it will apply to any workbook opened after the add-in starts up.

What error are you getting?