April 23, 2015
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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
Answers Post
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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.
1 Guest(s)