CODEWITHSUNDEEP
vbaexceldebuggingcheckbox
Garrett Linville
Garrett Linville

Reputation: 3

Excel Checkboxes move on each spreadsheet

I was having a problem with the "bug" that moves checkboxes around

to solve this I wrote a quick little vba code to keep them in line

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With Sheet3.Shapes("Group 5")
    .Top = Range("B6").Top + Range("B6").Height / 2 - .Height / 2
    .Left = Range("B6").Left + Range("B6").Left / 2
End With

End Sub

This works fine but if below the Group 5 I add a shape from sheet 4, They will move when I don't want them to.

So if I save on sheet 3, all of sheet 3's will move to the correct location (Which is good). Then if I click over to sheet 4, they are shifted a tad bit and visa versa. Does anyone know why this would be happening?

Thanks.

Upvotes: 0

Views: 439

Answers (2)

John F
John F

Reputation: 276

I hope I am understanding your issue correctly. If you want a shape to stay in the same spot relative to the worksheet borders not matter what happens in worksheet, you can set the Object Positioning option to Don't move or size with cells. You do this by right-clicking on the shape, click Format Control then select the Properties tab, and you will see the option there. Then you shouldn't need VBA code to adjust it.

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

Since you're using With, adjust that with this:

With Sheet3
    .Shapes("Group 5").Top = .Range("B6").Top + Range("B6").Height / 2 - .Height / 2
    .Shapes("Group 5").Left = .Range("B6").Left + Range("B6").Left / 2
End With

I think that should work, if not, just add "Sheet3." before the two Range("B6") lines. THis would make it explicit that the range used is that sheet. I think what's happening is when you switch sheets, that range changes to the B6 on the new sheet you activated.

edit: Also, I assume "Sheet3" is a worksheet variable? Otherwise, you need Sheets("Sheet3")

Upvotes: 0

Related Questions