Excel - Hide/Unhide rows based on a cell value.

[BYTEr]

Hi all,

I'm creating a checklist with Yes/No questions using option buttons, these are also cell linked with the value being FALSE if option button is not ticked or TRUE if the option button has been ticked.

Sometimes, depending on the answer a user gives, I might want the question in the next row to hide, or another question to unhide for the user to answer.

Hopefully that makes sense.

So essentially, what I want are rows automatically hiding/unhiding based on a cell value of TRUE or FALSE.

I'm not particularly skilled with VBA but can usually google and copy/paste code but I haven't been able to crack this.

Any help would be appreciated.

Thanks in advance.

 

[bremen1874]

this? https://analysistabs.com/excel-vba/hide-unhide-rows-worksheet/#bm1

 

[BYTEr]

this? https://analysistabs.com/excel-vba/hide-unhide-rows-worksheet/#bm1

I did look at that, but I couldn't get it to work.

I might have found a slightly different solution, by using an Active X Control checkbox and the following code:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
    [5:10].EntireRow.Hidden = False
    Else: [5:10].EntireRow.Hidden = True
End If
End Sub

Seems to work.

 

[bremen1874]

A slightly condensed version

Private Sub CheckBox1_Click()
    [5:10].EntireRow.Hidden = (CheckBox1 = False)
End Sub

 

[BYTEr]

Thanks bremen1874,

I don't suppose you know the code for a macro button for unticking all control boxes? or certain control boxes?

 

[BYTEr]

Thanks bremen1874,

I don't suppose you know the code for a macro button for unticking all control boxes? or certain control boxes?

Or even better, if certain check boxes become unticked by unticking a certain check box.

 

[Enfield]

Are the checkbox's in a userform or are they in a sheet?

If they're in a sheet, try this.

If you really want you could send your spreadsheet to my email address in Trust (I write VBA for a living so a little more won't hurt me).

Private Sub CertainCheckBox_Click()

    Dim ctl As Object
  
    If CertainCheckBox Then Exit Sub
  
    For Each ctl In ActiveSheet.OLEObjects
  
        If TypeName(ctl.Object) = "CheckBox" Then
      
            Select Case ctl.Name
          
                Case "chk2", "chk3"
              
                    ' Ignore these checkboxes
                  
                Case Else
              
                    ctl.Object.Value = False
            End Select
        End If
    Next ctl
End Sub

 

[BYTEr]

Hi Enfield,

Thanks for your response and offer, but I sorted the above out with a command button and the following code:

Private Sub CommandButton1_Click()

    Dim ws As Worksheet
    Dim xbox As OLEObject
    For Each ws In ThisWorkbook.Worksheets
        For Each xbox In ws.OLEObjects
            ws.OLEObjects(xbox.Name).Object.Value = False
        Next
    Next
End Sub

Which worked fine for my needs.

However I've now run into an absolute catastrophe.

I've opened the sheet today to find all my Active X Checkboxes (50+) that were hidden with the rows have disappeared, or rather have a height of 0 and positioning all stacked in the same cell.

After some reading, this seems to be a known bug with no real fix.

Apparently though, if all the rows are unhidden before closing, this doesn't cause a problem.

So what I need is some code that will unhide all rows before closing, but then I'll also need some code to hide certain rows when I reopen the sheet to have the default view I want, if that makes sense?

Is this possible?

Thanks in advance.

 

[Enfield]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    With wksChangeMeToCodeNameOfRelevantSheet
    
        .UsedRange.Rows.Hidden = False
    End With
 
    ThisWorkbook.Close SaveChanges:=True
End Sub

Here's part one.

Sounds like you need to record the hidden row numbers and write them to another sheet. Read the sheet when the workbook opens, then re-hide the rows.

I'll have a go later if you want.

The code you used above will error if you have certain other controls on your spreadsheet by the way, or if you have a textbox or combox it will change the value to say 'FALSE'. It will also uncheck every checkbox rather than certain ones.

 

[BYTEr]

Thanks again, much appreciated.

Just in case it matters, the sheet is protected by a password, but formatting of rows is not protected (otherwise users wouldn't be able to tick the checkboxes to unhide/hide rows).

I'll just need to remember before logging off today to unhide all rows, then hopefully tomorrow the checkboxes wouldn't of moved/shrunk and the cause of this is that checkboxes are on hidden rows, otherwise I'd have to use a different method because of this bug.

The other option I suppose, which sounds like far more work, is if you could somehow run a macro stating the location of each checkbox and its height and have this run on startup, then it wouldn't matter if the checkboxes moved/changed heights.

At the moment the rows that need hiding on startup is.
6-15
18-23
26-31
34-39
42-47
50-55
58-65
68-74

Thanks again!

 

[Enfield]

Option Explicit

Private Sub Workbook_Open()

    With wksChangeMeToCodeNameOfRelevantSheet
    
        .Rows("5:10").EntireRow.Hidden = True
    End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    With wksChangeMeToCodeNameOfRelevantSheet
    
        .UsedRange.Rows.Hidden = False
    End With
 
    ThisWorkbook.Close SaveChanges:=True
End Sub

Add the other rows and change the With statements to include the codename of the relevant sheet. You can get the codename of the activesheet by typing the below into the immediate window.

?ActiveSheet.Codename

 

[BYTEr]

Hi Enfield,

Just tried it, works flawlessly! Thank you very much.

 

[Enfield]

No worries.