Hello Everyone,
I have a multiple sheet workbook and I am trying to have a series of identical buttons, rectangles, and text boxes on each sheet in exactly the same position.
I start by arranging sheet 1 as I want it and then I use vba to 'take a picture' of the positions, (nameLoc is a range with the type of shape, and nameNumber is the shape number)
This gives me a grid with all the parameters. I then reverse the process to size and position the shapes on the other sheets, thusly:
It the column widths are identical, it works perfectly--you can move from one sheet to another and you cannot detect a difference in psitions; but if the column widths are different, then there will be minor, but easily perceptible, differences in most of the left positions and the widths.
Any ideas or suggestions?
Many thanks,
David
I have a multiple sheet workbook and I am trying to have a series of identical buttons, rectangles, and text boxes on each sheet in exactly the same position.
I start by arranging sheet 1 as I want it and then I use vba to 'take a picture' of the positions, (nameLoc is a range with the type of shape, and nameNumber is the shape number)
Code:
Do
Set nameNumber = nameLoc.Offset(0, y)
nameLoc.Offset(0, 1).Value = ActiveSheet.Shapes(nameLoc.Value & " " & nameNumber.Value).Top
nameLoc.Offset(0, 2).Value = ActiveSheet.Shapes(nameLoc.Value & " " & nameNumber.Value).Left
nameLoc.Offset(0, 3).Value = ActiveSheet.Shapes(nameLoc.Value & " " & nameNumber.Value).Height
nameLoc.Offset(0, 4).Value = ActiveSheet.Shapes(nameLoc.Value & " " & nameNumber.Value).Width
Set nameLoc = nameLoc.Offset(1, 0)
Loop Until nameLoc.Value = ""
Code:
Do
Set nameNumber = nameLoc.Offset(0, y)
ActiveSheet.Shapes(nameLoc.Value & " " & nameNumber.Value).Top = nameLoc.Offset(0, 1).Value
ActiveSheet.Shapes(nameLoc.Value & " " & nameNumber.Value).Left = nameLoc.Offset(0, 2).Value
ActiveSheet.Shapes(nameLoc.Value & " " & nameNumber.Value).Height = nameLoc.Offset(0, 3).Value
ActiveSheet.Shapes(nameLoc.Value & " " & nameNumber.Value).Width = nameLoc.Offset(0, 4).Value
Set nameLoc = nameLoc.Offset(1, 0)
Loop Until nameLoc.Value = ""
Any ideas or suggestions?
Many thanks,
David