Hide Rows just before file is saved via commandbutton doesn't work

[roelandwatteeuw]

Ok, I'm getting nuts.
I have an Excel file with Checkboxes on sheet 'RBD'
I have a checkbox for each title. And each title has several checkboxes for the options

|_| ANIMALS
....|_| Cow
....|_| Pig
....|_| Chicken
....|_| Other


|_| FLOWERS
....|_| Rose
....|_| Tulip
....|_| Sunflower
....|_| Other


The Checkboxes are connected to cells in Colomn M. (Checkbox on --> colomn M = True)
The Checkbox for the title (Animals) is on row 2
The Checkboxes for the options (cow, pig...) are on rows 3 to 7
The Checkboxes are numbered 220 to 223




If one or more of the options are checked, the main title need to be checked and all rows under this title must be visible.
If none of the options are checked, the main title need to be unchecked and all rows under this title must be hidden.


This must happen just before the file is saved.
Users save the file with a Commandbutton. This will do some handlings and will finally save the file on the right place and with the correct name.


Ok, now the strange part:
If I run the code separatly (sub HideTitles), it works perfect.
If the code runs by normal saving (So via sub BeforeSave), it works perfect as well.
BUT when I save the file with the Commandbutton, everthing works (checkboxes hide, titles are checked/uncheck...), everything except the rows don't hide.

My code:

Private Sub CommandButton2_Click()


Path = "C:\Users\roela\Desktop\"     ''''edit this!!
FileName = "SRBD"


Sheets("RBD").Range("A1").Value = 1
ActiveWorkbook.SaveAs Path & " " & FileName & ".xlsm"


Shell "explorer.exe " & Path, vbNormalFocus


End Sub

Above code activates the BeforeSave

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


     HideTitles


 End Sub

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


   
If Sheets("RBD").Range("A1") <> 1 Then
    Cancel = True


    MsgBox "The file can't be saved" & vbCrLf _
            & "Use the red button to save the file"
Else
    HideTitles
End If


Sheets("RBD").Range("A1").Value = ""


End Sub








Private Sub HideTitles()
If Sheets("RBD").ProtectContents = True Then
        Sheets("RBD").Unprotect
End If


'TITLES ANIMALS
If Sheets("RBD").Range("M3").Value = True Or Sheets("RBD").Range("M4").Value = True Or Sheets("RBD").Range("M5").Value = True Or Sheets("RBD").Range("M6").Value = True Then
'Checkbox ON - Main Title
    Sheets("RBD").Range("M2").Value = True
'Show Rows 3 to 7
    If Sheets("RBD").Rows("3:7").Hidden = True Then
        Sheets("RBD").Rows("3:7").EntireRow.Hidden = bShow
    End If
'Show + resize Checkboxes
    For x = 220 To 223
        Sheets("RBD").Shapes.Range(Array("Check Box " & x)).Visible = True
        Sheets("RBD").Shapes("Check Box " & x).Height = 17
    Next x
Else
'Checkbox OFF - Main Title
    Sheets("RBD").Range("M2").Value = False
'Hide Rows 3 to 7
        Sheets("RBD").Rows("3:7").EntireRow.Hidden = Not bShow
'Hide Checkboxes
    For x = 220 To 223
        Sheets("RBD").Shapes.Range(Array("Check Box " & x)).Visible = False
    Next x
End If






'TITLE FLOWERS
If Sheets("RBD").Range("M11").Value = True Or Sheets("RBD").Range("M12").Value = True Or Sheets("RBD").Range("M13").Value = True Or Sheets("RBD").Range("M14").Value = True Then
'Checkbox ON - Main Title
    Sheets("RBD").Range("M9").Value = True
'Show Rows 10 to 15
    If Sheets("RBD").Rows("10:15").Hidden = True Then
        Sheets("RBD").Rows("10:15").EntireRow.Hidden = bShow
    End If
'Show + resize Checkboxes
    For x = 224 To 227
        Sheets("RBD").Shapes.Range(Array("Check Box " & x)).Visible = True
        Sheets("RBD").Shapes("Check Box " & x).Height = 17
    Next x
Else
'Checkbox OFF - Main Title
    Sheets("RBD").Range("M9").Value = False
'Hide Rows 10 to 15
    If Sheets("RBD").Rows("10:15").Hidden = NotbShow Then
        Sheets("RBD").Rows("10:15").EntireRow.Hidden = Not bShow
    End If
'Hide Checkboxes
    For x = 224 To 227
        Sheets("RBD").Shapes.Range(Array("Check Box " & x)).Visible = False
    Next x
End If


Sheets("RBD").Protect
End Sub

WHY? WHY???


I thought it was because the file was protected, but even without the protection on, it doesn't work + it does work with the protection on when I run the code (HideTitles) separate.


If I pause the macro just before the code to hide the rows, the protection is still on and I can't get it off with a vba-Code ( Sheets("RBD").Unprotect ).
I can switch it off manually (with the normal Excel-button), but the rows even won't hide after that.


So the protection doesn't change a thing (I think).

Someone knows the reason and more important, a solution?


You can download the file here:

online backup

 

[roelandwatteeuw]

Nobody an idea why it doesn't work?
Maybe my question was a bit too long

In my original post I inserted a similar file.

I want to hide all the rows under a title if none of the options was crossed on.
If one or more boxes are on, I need the rows to show up again.

It works perfect when I let the Sub 'ShowTitles' run by it's own.
Also no problem when I run the Sub 'Workbook_BeforeSave' (One of the action under this Sub is to run Sub 'ShowTitles')
But, when I run the Sub 'CommandButton2_Click', the rows don't hide. (One of the action under this Sub is to save the file, which will run Sub 'Workbook_BeforeSave', and that one will start 'ShowTitles')

Does anyone knows why the rows don't hide with the Sub 'CommandButton2_Click'?

Thanks!