Hi there, I am trying to use the code pictured below to keep slicers on top when I scroll through a worksheet, unfortunately it currently executes against all worksheets in the workbook, what is the easiest way to restrict it to one worksheet - Please see below...
If ActiveSheet.Name < "Task Tracker" Then Exit Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name < "Task Tracker" Then Exit Sub
Dim ShF As Shape
Dim ShM As Shape
'specify a slicer
Application.ScreenUpdating = False
Set ShF = ActiveSheet.Shapes("Project")
Set ShM = ActiveSheet.Shapes("Task")
'change position of the slicer
With Windows(1).VisibleRange.Cells(1, 1)
ShF.Top = .Top
ShF.Left = .Left + 500
ShM.Top = .Top
ShM.Left = .Left + 650
End With
Application.ScreenUpdating = True
End Sub
Hi Alan,
The code should look like this: (noticed that the code is in ThisWorkbook)
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "Task Tracker" Then Exit Sub
Dim ShF As Shape
Dim ShM As Shape
'specify a slicer
Application.ScreenUpdating = False
Set ShF = Sh.Shapes("Project")
Set ShM = Sh.Shapes("Task")
'change position of the slicer
With Windows(1).VisibleRange.Cells(1, 1)
ShF.Top = .Top
ShF.Left = .Left + 500
ShM.Top = .Top
ShM.Left = .Left + 650
End With
Application.ScreenUpdating = True
End Sub
Hi Catalin,
Your solution worked a treat - many thanks. Just one quick additional question, you mentioned that I had the code in the "This Workbook" section should it have been generated elsewhere?
Cheers,
Alan
No, this is a standard event for this workbook module:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
There is a similar event for each sheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range), you can use it to trigger sheet specific codes if you need that.
