Hide row if no selections are present

[ABabeNChrist]

I use both of these codes that are listed below
The first code I use to insert so desired comment to the Summary page

Code: Select all

    Dim blnSelected As Boolean

    ActiveWorkbook.Protect Password:="", Structure:=False, Windows:=False
    Sheets("Summary").Unprotect ""

    If CheckBox1.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A190:A191").EntireRow.Hidden = False
        Sheets("Summary").Range("A190").Value = Range("A8").Value & _
                                                ": " & Range("A11").Value
        Sheets("Summary").Range("A191").Value = " • " & Range("A19").Value
        Sheets("Summary").Range("A191").EntireRow.AutoFit
        Sheets("Summary").Range("A189").EntireRow.Hidden = False 'This row is labeled Heater


    End If

    If CheckBox2.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A192:A193").EntireRow.Hidden = False
        Sheets("Summary").Range("A192").Value = Range("A22").Value & _
                                                ": " & Range("A25").Value
        Sheets("Summary").Range("A193").Value = " • " & Range("A33").Value
        Sheets("Summary").Range("A193").EntireRow.AutoFit
        Sheets("Summary").Range("A189").EntireRow.Hidden = False 'This row is labeled Heater

    End If

    If CheckBox3.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A194:A195").EntireRow.Hidden = False
        Sheets("Summary").Range("A194").Value = Range("A37").Value & _
                                                ": " & Range("A40").Value
        Sheets("Summary").Range("A195").Value = " • " & Range("A47").Value
        Sheets("Summary").Range("A195").EntireRow.AutoFit
        Sheets("Summary").Range("A189").EntireRow.Hidden = False 'This row is labeled Heater

    End If

    If CheckBox4.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A196:A197").EntireRow.Hidden = False
        Sheets("Summary").Range("A196").Value = Range("A50").Value & _
                                                ": " & Range("A53").Value
        Sheets("Summary").Range("A197").Value = " • " & Range("A61").Value
        Sheets("Summary").Range("A197").EntireRow.AutoFit
        Sheets("Summary").Range("A189").EntireRow.Hidden = False 'This row is labeled Heater

    End If

    Sheets("Summary").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                              Password:="", UserInterfaceOnly:=True


    ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True
    
    If blnSelected = True Then
        MsgBox "Your selection(s) have now been added to Report Summary page"
        Unload Me
        Unload UserForm17
    Else
        MsgBox "No check boxes were selected"
    End If

The second code will remove comment if so desired

Code: Select all

    Dim blnSelected As Boolean

    ActiveWorkbook.Protect Password:="", Structure:=False, Windows:=False
    Sheets("Summary").Unprotect ""

    If CheckBox1.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A190:A191").EntireRow.Hidden = True
        Sheets("Summary").Range("A190").Value = ""
        Sheets("Summary").Range("A191").Value = ""


    End If

    If CheckBox2.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A192:A193").EntireRow.Hidden = True
        Sheets("Summary").Range("A192").Value = ""
        Sheets("Summary").Range("A193").Value = ""

    End If

    If CheckBox3.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A194:A195").EntireRow.Hidden = True
        Sheets("Summary").Range("A194").Value = ""
        Sheets("Summary").Range("A195").Value = ""

    End If

    If CheckBox4.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A196:A197").EntireRow.Hidden = True
        Sheets("Summary").Range("A196").Value = ""
        Sheets("Summary").Range("A197").Value = ""

    End If

    Sheets("Summary").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                              Password:="", UserInterfaceOnly:=True


    ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True
    
    If blnSelected = True Then
        MsgBox "Your selection(s) have now been removed from Report Summary page"
        Unload Me
        Unload UserForm17
    Else
        MsgBox "No check boxes were selected"
    End If

Everything works great except
Let me give you an example, if I were to insert a single comment on to the Summary page using the first code and then later decide to remove comment using the second code. It still leaves row A189 visible. I understand I could add a line of code to hide row by adding false. But let’s say I now add 2 comments and only remove 1. Now I have a dilemma.

[HansV]

Instead of using a True/False value blnSelected, you could use a number value intCount that keeps track of how many check boxes were ticked. If all four were ticked, you can hide row 189.

Code: Select all

    Dim intCount As Integer

    ActiveWorkbook.Protect Password:="", Structure:=False, Windows:=False
    Sheets("Summary").Unprotect ""

    If CheckBox1.Value = True Then
        intCount = intCount + 1
        Sheets("Summary").Range("A190:A191").EntireRow.Hidden = True
        Sheets("Summary").Range("A190").Value = ""
        Sheets("Summary").Range("A191").Value = ""
    End If

    If CheckBox2.Value = True Then
        intCount = intCount + 1
        Sheets("Summary").Range("A192:A193").EntireRow.Hidden = True
        Sheets("Summary").Range("A192").Value = ""
        Sheets("Summary").Range("A193").Value = ""
    End If

    If CheckBox3.Value = True Then
        intCount = intCount + 1
        Sheets("Summary").Range("A194:A195").EntireRow.Hidden = True
        Sheets("Summary").Range("A194").Value = ""
        Sheets("Summary").Range("A195").Value = ""
    End If

    If CheckBox4.Value = True Then
        intCount = intCount + 1
        Sheets("Summary").Range("A196:A197").EntireRow.Hidden = True
        Sheets("Summary").Range("A196").Value = ""
        Sheets("Summary").Range("A197").Value = ""
    End If

    If intCount = 4 Then
        ' All four check boxes were ticked
        Sheets("Summary").Range("A189").EntireRow.Hidden = True
    End If

    Sheets("Summary").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                              Password:="", UserInterfaceOnly:=True

    ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True

    If intCount > 0 Then
        MsgBox "Your selection(s) have now been removed from Report Summary page"
        Unload Me
        Unload UserForm17
    Else
        MsgBox "No check boxes were selected"
    End If

[ABabeNChrist]

Thank you Hans
I shall give this a try
I also just noticed that I should probably combine

Code: Select all

        Sheets("Summary").Range("A194").Value = ""
        Sheets("Summary").Range("A195").Value = ""

to something like

Code: Select all

        Sheets("Summary").Range("A194:A195").Value = ""

[HansV]

Or

Sheets("Summary").Range("A194:A195").ClearContents

[ABabeNChrist]

Hi Hans
I tried the code you suggested, it only seems to hide cell A189 only if all checks are selected. I may have 1 or more comments I wish to leave on Report Summary sheet, or may only have 1 comment and I want that removed
I hope I am explaining it Ok for you
Is there maybe a formula I could add to cell A189 so that if there is no value in any of the following cells like A190:A197 then cell A189 will be hidden
Or ?

[HansV]

Apparently I don't understand what your code does. Go back to your original version and add the following above the line that re-protects the worksheet:

Code: Select all

    Sheets("Summary").Range("A189").EntireRow.Hidden = _
        (Application.CountA(Sheets("Summary").Range("A190:A197")) = 0)

[ABabeNChrist]

Perfecto, works great, just what I was hoping for
Thank you Hans