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

roelandwatteeuw

New Member
Joined
Feb 20, 2015
Messages
42
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:
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
Code:
 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


     HideTitles


 End Sub



Code:
 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

New Member
Joined
Feb 20, 2015
Messages
42
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!

 

Forum statistics

Threads
1,081,990
Messages
5,362,584
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top