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

BYTEr · 12 Apr 2018 at 14:57

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.

BYTEr · 12 Apr 2018 at 15:26

bremen1874 said:
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:

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.

BYTEr · 12 Apr 2018 at 16:37

Thanks bremen1874,

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

BYTEr · 12 Apr 2018 at 17:21

BYTEr said:
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.

BYTEr · 16 Apr 2018 at 10:22

Hi Enfield,

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

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.

BYTEr · 16 Apr 2018 at 14:04

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!

BYTEr · 16 Apr 2018 at 16:43

Hi Enfield,

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