View Full Version : Solved: VBA: Converting Worksheet_change sub into Addin
surya prakash
02-18-2005, 10:40 PM
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
02-19-2005, 12:20 AM
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
02-20-2005, 11:12 PM
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
04-10-2019, 01:51 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.