Ask Your Question

Dynamic hide/unhide sheets based on cell value

asked 2019-10-30 20:39:38 +0100

damirb gravatar image

Let say that my workbook have sheets as follow: "Workbook content"; "1"; "2"; "3"; "4"; "5"; "6"; ...etc...

Column "A" in sheet named "Workbook content" is a list of sheets from workbook and it looks like: A1="1"; A2="2"; A3="3"; A4="4"; A5="5"; ...etc...

Now, I want to be able to dynamic hide or unhide sheets based on value In column "B" (or any other column on this "Workbook content" sheet). For example, if B1="Hide"; B2="Hide"; B3="Show"; B4="Hide"; B5="Show"; B6="Show" then sheets with name "1", "2", "4" should be hidden and other sheets ("3", "5" and "6") should be visible on sheet tabs. As I say, if is possible, system should be dynamic. How can I do this? I would be very grateful if someone can help me.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-10-31 16:11:52 +0100

Using a formula it's a bad idea. It's better if you using a macro. Next code does work.

Sub sheets(event)
    doc = ThisComponent

    If event.ImplementationName = "ScCellObj" Then
        ca = event.CellAddress
        If ca.Column = 1 Then
            so = event.SpreadSheet
            c = so.getCellByPosition(0, ca.Row)
            t = LCase(event.String)
            s = doc.Sheets.getByName(c.String)
            If t = "hide" Then
                v = False
            ElseIf t = "show" then
                v = True
                Exit Sub
            End If
            s.isVisible = v
        End If
    End If
End Sub

And set in event "Content changed" in your sheet "Workbook content"

Best regards

edit flag offensive delete link more


Hi Mauricio. I was trying to do this job with something like theCell=ThisComponent.CurrentSelection and then use thecell for sheet.isvisible=true/false but without wanted result. Your macro do exactly what I need and want...I just put "v=false" instead "Exit Sub" in your code to avoid any visible sheet except sheet with "Show" in column B for this example...very simple idea Mauricio, thanks a lot...I guess that this answer could have closed tag now...Best regards to you

damirb gravatar imagedamirb ( 2019-10-31 20:30:36 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-10-30 20:39:38 +0100

Seen: 828 times

Last updated: Oct 31 '19