Solved: VBA: Converting Worksheet_change sub into Addin

[surya prakash]

I am wondering if it possible to put
Private Sub Worksheet_Change(ByVal Target As Range) in a addin so that the sub is available to all the workbooks

[Jacob Hilderbrand]

Well in this case we want to use a worksheet event at the application level for each workbook. There is a workbook level worksheet event built in that we can use, but for the application level event we will need to create our own class.

Create a new workbook and put this code in the ThisWorkbook code module.


Option Explicit

Private Sub Workbook_Open()

Call ClassInitialize

End Sub

Insert a Standard Module and put in this code.


Option Explicit

Dim Wkbs() As New WorkBookClass
Dim App As New App

Public Sub ClassInitialize()

Dim i As Long
Dim Wkb As Workbook

For Each Wkb In Application.Workbooks
i = i + 1
ReDim Preserve Wkbs(1 To i)
Set Wkbs(i).WkbBook = Wkb
Next
Set App.App = Application

End Sub

Insert a Class Module and rename it to App. Then put in this code.


Option Explicit

Public WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)

Call ClassInitialize

End Sub

Create a Class Module and rename it to WorkBookClass. Then put in this code.


Option Explicit

Public WithEvents WkbBook As Workbook

Private Sub WkbBook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

MsgBox "Triggers are working properly:" & vbNewLine & _
"Workbook Name: " & WkbBook.Name & vbNewLine & _
"Worksheet Name: " & Sh.Name & vbNewLine & _
"Range Address: " & Target.Address(False, False)

End Sub

Change the code in this Class Module to the code you currently have for your event.

Then save the workbook as an Add-In.

See the attachment for more information.

[surya prakash]

Hello DRJ,
Thank you very much for the code;
Let me tryout your solution on my problem and see if it works.

thank you very much indeed.
Surya

[duldul]

Dear Jacob
can you create addin for this VBA

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
With .EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 22
End With
End With
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 27
End With

End Sub