Issue with moving a slicer shape

Gringoire

Board Regular
Joined
Nov 18, 2016
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Hello friends,
I wrote a simple sub to place my shapes at the right position in my sheet but I have some issue with slider positioning:
The instructions sl.Shape.Top = cTop and sl.Shape.Left = cLeft raise a Run Time error ' -21470224809(80070057) The specified value is out of range."
I also tried to assign the Top and Left values directly to sl (the Slicer) with no success (run time error 5).

The property .top and .Left works with all the oter shapes (combobox, buttons, checkbox, etc.) but not with Slicer shapes....
Can you please explain me where I'm doing wrong?

VBA Code:
Sub placeShapes()
    '=============================================
    'Place the shapes into the right positions
    '=============================================
  
    Dim cTop As Double, sp As Double, cLeft As Double
    cTop = 31: sp = 10
    With shAnalisi      'This is the sheet codename
        Call BtnResize(.cmbPivot, 8, cTop, .Columns(1).Width, 19, 215)      'combo box
        Call BtnResize(.btnAdvf, 8, cTop, .cmbPivot.Left + .cmbPivot.Width + sp, 18, 97)
        cLeft = .btnAdvf.Left + .btnAdvf.Width + sp
        Dim slC As SlicerCache, sl As slicer
        For Each slC In ActiveWorkbook.SlicerCaches
            For Each sl In slC.Slicers
                sl.Shape.Top = cTop
                sl.Shape.Left = cLeft
                cLeft = sl.Left + sl.Width + sp
            Next
        Next
    End With
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
EDIT:
If I call the slicer directly by his own name, the property top works correctly, but not inside the for...next cycle:
VBA Code:
ActiveWorkbook.SlicerCaches("SC_Transazioni").Slicers("TR").Top = 31
:unsure:
 
Upvote 0
For me it works as below:
VBA Code:
Sub placeShapes()
    Dim cTop As Double, sp As Double, cLeft As Double
    cTop = 31: sp = 10
    With Sheet1      'This is the sheet codename
        cLeft = 50
        Dim slC As SlicerCache, sl As Slicer
        For Each slC In ActiveWorkbook.SlicerCaches
            For Each sl In slC.Slicers
                sl.Shape.Top = cTop
                sl.Shape.Left = cLeft
                cLeft = sl.Left + sl.Width + sp
            Next
        Next
    End With
End Sub

Given that the above works, I would investigate the below:
VBA Code:
cLeft = .btnAdvf.Left + .btnAdvf.Width + sp
 
Upvote 0
As I don't know what btnAdvf is, it makes it difficult to help any further. If it is a shape then maybe it could be more like the below:
VBA Code:
Sub placeShapes()
    Dim cTop As Double, sp As Double, cLeft As Double
    cTop = 31: sp = 10
    With Sheet1      'This is the sheet codename
        cLeft = .Shapes("Button 2").Left + .Shapes("Button 2").Width + sp
        Dim slC As SlicerCache, sl As Slicer
        For Each slC In ActiveWorkbook.SlicerCaches
            For Each sl In slC.Slicers
                sl.Shape.Top = cTop
                sl.Shape.Left = cLeft
                cLeft = sl.Left + sl.Width + sp
            Next
        Next
    End With
End Sub
 
Upvote 0
I actually found the bug!
In a previous routine I set the slicer property .DisableMoveResizeUI = True
If i set DisableMoveResizeUI=False, everything works properly

BTW this behavior seems to be not compliant with the definition I found on microsoft site:
DisableMoveResizeUI

Returns or sets whether the specified slicer can be moved or resized by using the user interface. Read/write.
True if the slicer cannot be moved or resized by selecting borders or handles in the user interface; otherwise, False. The default value is False.
Setting the DisableMoveResizeUI property to True affects only the user interface.
Moving or resizing the slicer by setting properties, such as Top, Left, Width, or Height, from code is not disabled.
 
Upvote 0
Solution
I make you right, the statement from Microsoft seems to be wrong in this instance.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,023
Latest member
alabaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top