VBA: Hiding/unhiding checkboxes - error

[Bats Belfry]

Experts,

I have spent the greater part of my weekend working on a spreadsheet for my employer; I'm almost done save for an annoying little issue simplified in this file. Unfortunately I am not VBA savvy enough to identify the error.

What I try to achieve is to hide and unhide some checkboxes. Everything is fine first time I run the macro - both the 'hide' and 'unhide' part. But if I hide the checkboxes, save the file, close Excel, reopen the file, and attemps to unhide, the checkboxes will be squashed and useless. The mentioned file should display these characteristics.

What have I done wrong? Something with the formatting?

I appreciate any advice you might have. Thanks

Bats

 

[Ralajer]

Welcome to the board.

I see your problem and can't give you an answer to why exactly it is squashing your check boxes only after a save, close and reopen. Though, I can suggest a work around that should work.

Even though the provided file was just an example I assume the check boxes are grouped on the actual sheet as well. Select the group and open its properties and look for the Object Position select Move but don't size with cells. Now depending on if want to hide the rows in addition to hiding the check boxes' group you may need to alter the following code.

Sub hide()
    ActiveSheet.Shapes.Range(Array("Group 1")).Visible = msoFalse
    '// Omit below if you don't need to hide the rows
    Rows("6:15").EntireRow.Hidden = True
End Sub

Sub unhide()
    ActiveSheet.Shapes.Range(Array("Group 1")).Visible = msoTrue
    '// Omit below if you don't need to hide the rows
    Rows("6:15").EntireRow.Hidden = False
End Sub

"Group 1" is the name of the group in the example file but you will have to open the selection pane to verify.

 

[Bats Belfry]

Excellent Ralajer. This works as intended. Thanks for your support.

 

[JDIAS]

Thank you Ralajer. I had the same problem and you helped me as well!