December 7, 2021
I have a procedure that loops through the slicers on a sheet. My goal is to align each slicer with its corresponding column. For example:
- Column A: Left =0 and Width = 30, so Slicer1 should also be Left=0 and Width=30
- Column B: Left =30 and Width = 10, so Slicer2 should also be Left=30 and Width=10
Sub LikeMagic() Dim ws As Worksheet Dim iNumShapes As Integer Dim iNumCharts As Integer Dim iNumSlicers As Integer Dim dLeft as Long Dim dWidth as Long Dim dTop as Long Dim dHeight as Long Dim shp As Shape Dim colShapes As New Collection Set ws = ActiveWorkbook.Worksheets(ActiveSheet.Index) iNumShapes = ws.Shapes.Count iNumCharts = ws.ChartObjects.Count If iNumShapes = 0 Then Set ws = Nothing: Exit Sub ' Count and add slicers to collection With ws For Each shp In ws.Shapes If shp.Type = msoSlicer Then Err.Clear iNumSlicers = iNumSlicers + 1 colShapes.Add shp colShapes.Item(iNumSlicers).locked = False ' Unlock slicer End If Next shp End With If iNumSlicers = 0 Then Set ws = Nothing: Exit Sub ' Slicer Top and Height are fixed dTop = 0 dHeight = Application.InchesToPoints(2) For lCnt = 1 To iNumSlicers ' Get column Left and Width dLeft = ws.Columns(lCnt).Left dWidth = Application.InchesToPoints(ws.Columns(lCnt).ColumnWidth) Debug.Print "Slicer #" & iNumSlicers & vbNewLine & _ "Slicer: " & colShapes.Item(lCnt).Name & vbNewLine & _ "Top=" & dTop & vbNewLine & _ "Height=" & dHeight & vbNewLine & _ "Left=" & dLeft & vbNewLine & _ "Width=" & dWidth & vbNewLine ' Align slicer with associated columns <------ THIS IS NOT WORKING colShapes.Item(lCnt).Top = dTop colShapes.Item(lCnt).Height = dHeight colShapes.Item(lCnt).Width = dWidth colShapes.Item(lCnt).Left = dLeft Next lCnt
Set ws = Nothing
November 8, 2013
The topic seems very similar to your other post. Before creating a new one, please try to provide feedback or implement the solution you have received via help desk or other posts like:
Make sure you pass to the item the correct data type.
colShapes.Item(lCnt).Top is a Single data type, you can check that in immediate window:
Therefore, declaring dTop as long type and assigning it to a shape .Top might raise a mismatch conflict. Same for other similar parameters (Left, Width)
When you're saying "it's not working", what this means? You know we cannot test your code outside your environment (workbook), so this description will not describe anything, just leaves room for guessing, which is something a witch will do.
Please post the error messages you have, screenshots will be more helpful. A sample file that reproduces the error will be ideal.
Are there other codes that are calling this procedure? From previous posts, you have such calls between other unprotect/protect calls.