CODEWITHSUNDEEP
excelvba
FreeSoftwareServers
FreeSoftwareServers

Reputation: 2831

Make SheetSelectionChange operate on a different workbook

I'm trying to build an "add-in" for Excel that highlights the current row/column based on the activecell. I need it to be able to be toggled on/off and run from an xlam workbook that is obviously not the activeworkbook.

Currently, my code works, but only when I paste it under ThisWorkbook under the actual ActiveWorkbook. How can I create a Workbook_SheetSelectionChange that operates on all ActiveWorkbooks or "open" workbooks. IE: Any change in any active cell in any workbook. That would be ideal, but I'd be ok w/ just having it affect the ActiveWorkbook

I read through this link --> https://www.mrexcel.com/board/threads/make-sheetselectionchange-operate-on-a-different-workbook.888122/ which said something about creating a "class module" but I'm unfamiliar with that terminology/function.

Here is the code as it stands:

Public ReadMode As Boolean
Public Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim Count As Integer
Count = 0
While Count < 1 And ReadMode = True
Count = 2
'https://www.excel-easy.com/vba/examples/highlight-active-cell.html
Dim rowNumberValue As Integer, columnNumberValue As Integer, i As Integer, j As Integer
Cells.Interior.ColorIndex = 0
rowNumberValue = ActiveCell.Row
columnNumberValue = ActiveCell.Column
For i = 1 To rowNumberValue
    Cells(i, columnNumberValue).Interior.ColorIndex = 37
Next i
For j = 1 To columnNumberValue
    Cells(rowNumberValue, j).Interior.ColorIndex = 37
Next j
Wend
End Sub
'Public Sub ReadModeToggle(control As IRibbonControl)
Public Sub ReadModeToggle()
 If ReadMode = False Then
  Call ReadModeEnable_Sub
 ElseIf ReadMode = True Then
  Call ReadModeDisable_Sub
 End If
End Sub
Public Sub ReadModeEnable_Sub()
 ReadMode = True
End Sub
Public Sub ReadModeDisable_Sub()
 ReadMode = False
 Dim rowNumberValue As Integer, columnNumberValue As Integer, i As Integer, j As Integer
 Cells.Interior.ColorIndex = 0
 rowNumberValue = ActiveCell.Row
 columnNumberValue = ActiveCell.Column
 For i = 1 To rowNumberValue
  Cells(i, columnNumberValue).Interior.ColorIndex = 0
 Next i
 For j = 1 To columnNumberValue
  Cells(rowNumberValue, j).Interior.ColorIndex = 0
 Next j
End Sub

Upvotes: 0

Views: 127

Answers (1)

FreeSoftwareServers
FreeSoftwareServers

Reputation: 2831

Ok, so I modified the code like so:

Insert "Class Module"

Public WithEvents appevent As Application ''New Code
Private Sub appevent_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) ''New Code
Dim Count As Integer
Count = 0
While Count < 1 And ReadMode = True
Count = 2
'https://www.excel-easy.com/vba/examples/highlight-active-cell.html
Dim rowNumberValue As Integer, columnNumberValue As Integer, i As Integer, j As Integer
Cells.Interior.ColorIndex = 0
rowNumberValue = ActiveCell.Row
columnNumberValue = ActiveCell.Column
For i = 1 To rowNumberValue
    Cells(i, columnNumberValue).Interior.ColorIndex = 37
Next i
For j = 1 To columnNumberValue
    Cells(rowNumberValue, j).Interior.ColorIndex = 37
Next j
Wend
End Sub

Updated "Module" code:

Public myobject As New Class1 ''New Code
Public ReadMode As Boolean
'Public Sub ReadModeToggle(control As IRibbonControl)
Public Sub ReadModeToggle()
Set myobject.appevent = Application ''New Code
 If ReadMode = False Then
  Call ReadModeEnable_Sub
 ElseIf ReadMode = True Then
  Call ReadModeDisable_Sub
 End If
 Debug.Print "ReadMode = " & ReadMode
End Sub
Public Sub ReadModeEnable_Sub()
 ReadMode = True
End Sub
Public Sub ReadModeDisable_Sub()
 ReadMode = False
 Dim rowNumberValue As Integer, columnNumberValue As Integer, i As Integer, j As Integer
 Cells.Interior.ColorIndex = 0
 rowNumberValue = ActiveCell.Row
 columnNumberValue = ActiveCell.Column
 For i = 1 To rowNumberValue
  Cells(i, columnNumberValue).Interior.ColorIndex = 0
 Next i
 For j = 1 To columnNumberValue
  Cells(rowNumberValue, j).Interior.ColorIndex = 0
 Next j
End Sub

Upvotes: 1

Related Questions