+ Reply to Thread
Results 1 to 3 of 3

Align Slicers on worksheet with Macro

Hybrid View

  1. #1
    Registered User
    Join Date
    10-15-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Align Slicers on worksheet with Macro

    I create repeatable reports where many slicers are used. The format calls for the slicer right and left edges to be butted up to one another, aligned top, same height. I current have to select all and distributed horizontally and adjust from there, seems fastest. The trick doing it that way is approximating the location of the left and right slicer, then manually adjust from there to make the right and left edges of the slicers meet. Placing them one at a time using snap to object is cumbersome too.
    I would like to create a macro that would select all the slicers, align by top, then snap the left edge of subsequent slicers to the right edge of the previous, distributing them to the right. I know it is possible and have seen other programs that do it, but don't know where to begin. Any help is greatly appreciated.
    (example attached)
    Thanks,
    Dave
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Align Slicers on worksheet with Macro

    So you will need to align the first slicer, and the macro will do the rest.

    Sub AutoArrangeSlicers()
    
    ' Places the slicers on the current sheet side by side,
    ' aligned right next to the upper left first slicer
    
        Dim objSlicerCache As SlicerCache
        Dim objSlicer As Slicer
        Dim objSlicerMostLeft As Slicer
        
        Dim lFirstTopPosition As Long
        Dim lFirstLeftPosition As Long
        Dim lFirstWidth As Long
        Dim lNewLeft As Long
        Dim lGapWidth As Long
        Dim lNewSlicerWidth As Long
    
    
        lGapWidth = -1 ' set the gap width between the slicers
        lNewSlicerWidth = 0 ' set to a size > 0 to set the same width to all slicers
        ' set to 0 to keep the original width of the slicers
        
        
        For Each objSlicerCache In ActiveWorkbook.SlicerCaches
            For Each objSlicer In objSlicerCache.Slicers
                If objSlicer.Shape.TopLeftCell.Worksheet.Name = ActiveSheet.Name Then
                    If lNewSlicerWidth > 0 Then
                    ' set the new same width to all slicers
                    objSlicer.Width = lNewSlicerWidth
                    End If
                        If objSlicerMostLeft Is Nothing Then
                            Set objSlicerMostLeft = objSlicer
                            lFirstTopPosition = objSlicer.Top
                            lFirstLeftPosition = objSlicer.Left
                            lFirstWidth = objSlicer.Width
                        Else
                        ' verify
                            If lFirstLeftPosition > objSlicer.Left Then
                                ' we've got a new one to the left, update info
                                Set objSlicerMostLeft = objSlicer
                                lFirstTopPosition = objSlicer.Top
                                lFirstLeftPosition = objSlicer.Left
                                lFirstWidth = objSlicer.Width
                            Else
                            ' skip
                            End If
                        End If
                End If
            Next objSlicer
        Next objSlicerCache
        
        ' Okay, we've got the most left position.
        ' Now, loop through all slicers again and position them right next to the first one
        ' with a small gap
        
        lNewLeft = lFirstLeftPosition + lFirstWidth + lGapWidth
        
        For Each objSlicerCache In ActiveWorkbook.SlicerCaches
            For Each objSlicer In objSlicerCache.Slicers
                If objSlicer.Shape.TopLeftCell.Worksheet.Name = ActiveSheet.Name Then
                
                    If objSlicer.Name = objSlicerMostLeft.Name Then
                        ' skip
                        Else
                            ' process
                            objSlicer.Top = lFirstTopPosition
                            objSlicer.Left = lNewLeft
                            lNewLeft = objSlicer.Left + objSlicer.Width + lGapWidth
                    End If
                
                End If
            Next objSlicer
        Next objSlicerCache
    
    End Sub
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Align Slicers on worksheet with Macro

    Sub AlignSlicers()
    
        Dim SlicerBox As Slicer
        Dim Left As Long
        Dim Height As Long
        Dim Top As Long
            
        Left = 100
        Top = 10
        Height = 200
        For Each SlicerBox In Sheet4.PivotTables(1).Slicers
            With SlicerBox
                .Left = Left
                .Top = Top
                .Height = Height
                Left = .Left + .Width
            End With
        Next
        
    End Sub
    You may need to use alternative looping approach if the slicers are not in the desired order
    Cheers
    Andy
    www.andypope.info

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to Run when Slicer Selection is Changed (multiple slicers)
    By jcanlas in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-09-2021, 11:41 AM
  2. Hidding/Locking [Protecting Worksheet] & Slicers issue
    By mattm11 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-14-2014, 01:22 AM
  3. Excel 2010 Slicers : use vba macro to only select last date listed ( always Now - 1 )
    By spencerpritchard in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-06-2014, 02:25 PM
  4. Can a macro make selections to slicers based on values or criteria in cells?
    By Yas420 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-26-2013, 03:07 PM
  5. [SOLVED] What is short-Cut forleft align and Right align?
    By Sandy in forum Excel General
    Replies: 1
    Last Post: 02-24-2005, 09:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1