Edit MACRO for all SHEETS

  • Thread starter Thread starter DAA
  • Start date Start date
D

DAA

The macro below changes the cell pointer color. However,
it only works on A sheet in a workbook. I need the macro
to work in ALL the sheets in the workbook without pasting
the macro on all worksheet modules.

Public OldRng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
If Not OldRng Is Nothing Then
OldRng.Interior.ColorIndex = xlNone
End If
Target.Interior.ColorIndex = 6
Set OldRng = Target
End Sub

Please edit MACRO to work on all sheets. Thanks
 
Not entirely an answer but at least the following method means a common
procedure can be modified which then applies to all sheets with the smaller
(common) code behind each sheet

I put your code into a module (respecify as a Public procedure not Private)
and changed the parameter as shown

Public OldRng As Range
Public Sub WSChange(Target)
If Not OldRng Is Nothing Then
OldRng.Interior.ColorIndex = xlNone
End If
Target.Interior.ColorIndex = 6
Set OldRng = Target
End Sub

I then put the following code behind each worksheet (same for all
worksheets) where you wish this to apply .......

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call WSChange(Target)
End Sub

If you allow users to create new worksheets then the module needs another
procedure to automatically populate the above code behind it, which would be
initiated by a workbook level event.

Cheers
Nigel
 
Hi DAA,

Excel also provides workbook wide sheet events. So put this code in the
ThisWorkbook code module

Public OldRng

Private Sub Workbook_Open()
ReDim OldRng(20) 'allow for upto 20 worksheets
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Dim ThisRng

On Error Resume Next
Set ThisRng = OldRng(Sh.Index)
If Not ThisRng Is Nothing Then
ThisRng.Interior.ColorIndex = xlNone
End If
Target.Interior.ColorIndex = 6
Set OldRng(Sh.Index) = Target

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob,

I copied and pasted the 2 macros you provided in the
WORKBOOK module. It gave me an error message "invalid
Outside procedure". It high-lighted the "ReDim OldRng(20)"

Can you please take a look again? Thanks.

Regards,
DAA
 
Hi Bob,
It's working perfectly! Please disregard my previous
posting.
Thank you for your time and effort.
Regards,
DAA
 
Phew! Had me worried there.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top