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
End Sub
Hi Shawn,
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.
For example:
colShapes.Item(lCnt).Top is a Single data type, you can check that in immediate window:
?TypeName(colShapes.Item(lCnt).Top)
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.
