Can I move an existing Excel button to a specific loc?

[MCubitt]

I have three buttons in Excel, on the worksheet. I want to move them to a particular location, irrespective of where they are currently.

If I try and record macro/move them and check the script, it's relative steps left and up:

    ActiveSheet.Shapes("CopyFrom").Select
    Selection.ShapeRange.IncrementLeft -109.5
    Selection.ShapeRange.IncrementTop 0.75

Any ideas anyone? Please?




Applications Support
UK

 

[VBAjedi]

You should be able to simply set the buttons Top and Left properties to the desired location. Say you wanted to position a button with the code name Button1 over cell G10:

Button1.Top = Range("G10").Top
Button1.Left = Range("G10").Left

VBAjedi

 

[MCubitt]

Thanks, that worked. Along with that and some fiddling, it worked:

  ' Put buttons back
    Worksheets("Users to add to core").Activate
    ActiveSheet.Shapes.Range(Array("CopyFrom", "ClearWS", "ExportCSV")).Select
    Selection.ShapeRange.LockAspectRatio = msoFalse
    Selection.ShapeRange.Height = 36
    Selection.ShapeRange.Width = 110
    
    Worksheets("Users to add to core").CopyFrom.Top = Range("C1").Top
    Worksheets("Users to add to core").CopyFrom.Left = Range("C1").Left
    Worksheets("Users to add to core").ClearWS.Top = Range("C1").Top
    Worksheets("Users to add to core").ClearWS.Left = Range("C1").Left
    Worksheets("Users to add to core").ExportCSV.Top = Range("C1").Top
    Worksheets("Users to add to core").ExportCSV.Left = Range("C1").Left
    
    ActiveSheet.Shapes("ExportCSV").Select
    Selection.ShapeRange.IncrementLeft -93#
    Selection.ShapeRange.IncrementTop 102#
    ActiveSheet.Shapes("ClearWS").Select
    Selection.ShapeRange.IncrementLeft -93#
    Selection.ShapeRange.IncrementTop 52.5
    ActiveSheet.Shapes("CopyFrom").Select
    Selection.ShapeRange.IncrementLeft -93#
    Selection.ShapeRange.IncrementTop 6.75

    Worksheets("Master").Activate

thanks for your help, Merry Christmas.



Applications Support
UK