E
ExcelMonkey
I it possible to to have event handlers for a given spreadsheet in an VBA
..xla Add-in? That is, I know I can use the code below in a given workbook
by inserting the first part in the Thisworkbook and the second part in a
regular code module. Is it possible to duplicate this into an xla and if so,
where would you put it. I am assuming that you will need code in the .xla
which identifies the active workbook as well.
'***************************************
'ThisWorkbook module
Option Explicit
Dim vOldVal As Double
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Application
..ScreenUpdating = False
..EnableEvents = False
..Calculation = xlCalculationManual
End With
Call LogChanges(vOldVal, Target)
With Application
..ScreenUpdating = True
..EnableEvents = True
..Calculation = xlCalculationAutomatic
End With
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
vOldVal = Target.Value
End Sub
'*******************************************
'Regular Code Modul
Option Explicit
Sub LogChanges(ByVal vOldVal, ByVal Target As Range)
Dim x As Double
Dim y As Double
If Target.Cells.Count > 1 Then Exit Sub
'On Error Resume Next
x = Target.Value
If IsEmpty(vOldVal) Then vOldVal = "[Empty Cell]"
y = vOldVal
vOldVal = vbNullString
On Error GoTo 0
End Sub
..xla Add-in? That is, I know I can use the code below in a given workbook
by inserting the first part in the Thisworkbook and the second part in a
regular code module. Is it possible to duplicate this into an xla and if so,
where would you put it. I am assuming that you will need code in the .xla
which identifies the active workbook as well.
'***************************************
'ThisWorkbook module
Option Explicit
Dim vOldVal As Double
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Application
..ScreenUpdating = False
..EnableEvents = False
..Calculation = xlCalculationManual
End With
Call LogChanges(vOldVal, Target)
With Application
..ScreenUpdating = True
..EnableEvents = True
..Calculation = xlCalculationAutomatic
End With
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
vOldVal = Target.Value
End Sub
'*******************************************
'Regular Code Modul
Option Explicit
Sub LogChanges(ByVal vOldVal, ByVal Target As Range)
Dim x As Double
Dim y As Double
If Target.Cells.Count > 1 Then Exit Sub
'On Error Resume Next
x = Target.Value
If IsEmpty(vOldVal) Then vOldVal = "[Empty Cell]"
y = vOldVal
vOldVal = vbNullString
On Error GoTo 0
End Sub