Button positions not quite right

[davidam]

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)

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 = ""

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:

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 = ""

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

 

[thisoldman]

Does the sheet-to-sheet alignment change if the gridlines are turned off in both worksheets?

 

[davidam]

Very good! Problem is solved when I remove the headings for columns. Users will not see headings so this is a perfect solution. Many thanks! David

 

[davidam]

Interestingly, when the column headings are turned back on, the buttons seem to stay in the correct positions. So this appears to be a good fix. Create the map from one sheet; remove column headings; apply the map to other sheets and then, if needed, turn back on the column headings. This allows me to shift from one sheet to another and create the illusion of changing some of the sheet contents rather than the whole sheet since the graphical elements remain frozen in place from one sheet to the next.

 

[thisoldman]

I do hope you've found your solution—the problem is those pesky extra pixels added to widths and heights by row and column division markers.

I have occasionally anchored shapes to individual cells: I set the .Top and .Left of the shape to the .Top and .Left of a particular cell. That technique becomes useful when shapes are toggled between hidden and displayed.

 

[davidam]

Well, it seemed at first that I could set the button positions without the column headings and then turn on column headings and that they would stay in place. However, it seems that the columns do seem to have a magnetic pull or, as you suggest, they are influenced by the column dividers. One good thing is that, after setting the positions with headings turned off, I can turn column headings on and then back off again and all is well. I am going to try setting all the positions relative to column A. Do you have a modification to my code that would easily do that?

 

[thisoldman]

I don't have anything to give you out of the box. I also don't know how anchoring to A1 will work. Range("A1").Top and .Left are both zero.

On the off chance we've overlooked it, you do have the button properties set to "Don't move or size with cells", don't you?

 

[davidam]

Yes I have set the property correctly.
One more interesting thing for anyone reviewing this discussion. As I said earlier, if you turn off the column headings and then run the 'placement' code, you can get a good result where the graphics remain in exactly the same place as you switch between sheets. However, after turning column headings back on and then off a few times (still developing this), a few graphic items 'lost their way' and started to be in the (minutely) wrong place when switching sheets. Running the placement code again, even several times, failed to fix the problem. What finally worked was to delete the offending items and then copy/paste them from a different sheet and then run the placement code again. Hopefully the users will never turn on column headings when using the sheets and the problem will not reappear. I have it set so that they cannot save any changes to these sheets so that even if they do create this annoying problem on occasion, it should disappear when they close and then re-open the sheets.

 

[thisoldman]

This might be an alternative to deleting and recreating the buttons. Upon worksheet deactivation, resize the button to zero, move it to (0, 0) and then hide it. On worksheet activation, you bring the button back. In the affected worksheet module:

Private Sub Worksheet_Deactivate()
    ' Hide button
    With Shapes("Button 1")
        .Left = 0
        .Top = 0
        .Height = 0
        .Width = 0
        .Visible = msoFalse
    End With
    
End Sub
''''

Private Sub Worksheet_Activate()
    Const BTTN1_LEFT As Double = 47.25
    Const BTTN1_TOP As Double = 15
    Const BTTN1_HT As Double = 25
    Const BTTN1_WD As Double = 96.75
    
    ' Show button.
    With Shapes("Button 1")
        .Left = BTTN1_LEFT
        .Top = BTTN1_TOP
        .Height = BTTN1_HT
        .Width = BTTN1_WD
        .Visible = msoTrue
    End With
        
End Sub
''''

Well, it is nice to know someone besides me obsesses over the fussy, fussy details. Would you consider displaying a splash screen to the user? One with the message, "You are too close to the monitor! Please stand back."