Results 1 to 8 of 8

VBA code to hide/unhide sheets based on a tick-box (TRUE/FALSE)

Threaded View

  1. #1
    Registered User
    Join Date
    01-10-2013
    Location
    pune
    MS-Off Ver
    Excel 2010
    Posts
    5

    Lightbulb VBA code to hide/unhide sheets based on a tick-box (TRUE/FALSE)

    Hello everyone, this is my first post..

    I had written this code to show/hide worksheets based on check box of worksheet names.
    If the check box corresponding to a sheet says TRUE, the particular sheet is shown whereas the sheet is hidden if it is false.

    Problem: When I open the workbook and select a few sheets (corresponding check boxes) and press the button linked to this code, it works fine (and shows the selected sheets) but on further iterations (when I change the check boxes) it does not work (does not change the visibility of sheets) till I close and reopen the workbook.

    Program:
    Sub organize_report()
    
    Dim wS As Worksheet
    Dim tabFlag As Boolean
    Dim rNg As Range
    
    'If active workbook is different then do not run the code
    If ActiveWorkbook.Name <> ThisWorkbook.Name Then Exit Sub
    
    Set currwb = ThisWorkbook
    Set rNg = wksOrganize.Range("tabList")
    
    'Check which sheets to keep and make others "xlveryhidden"
    For Each wS In Worksheets
     If wS.Name = wksOrganize.Name Then GoTo EndLoop
    
     If IsError(Application.Match(wS.Name, rNg, 0)) Then
        wS.Visible = xlSheetHidden
     Else
        tabFlag = wksOrganize.Range("tabList_Anchor").Offset(Application.WorksheetFunction.Match(wS.Name, wksOrganize.Range("tabList"), 0), 3)
        If Not (tabFlag) Then
            wS.Visible = xlSheetHidden
        End If
     End If
    
    EndLoop:
    Next wS
    
    
    'Activate the first visible sheet
    For Each wS In Worksheets
    
    If wS.Visible = xlSheetVisible Then
    wS.Activate
    GoTo EndForHandler
    End If
    
    Next wS
    
    EndForHandler:
    
    End Sub

    Expectation:
    I should be able to refresh the selection and choose a different set of sheets.

    Regards,
    Kavindra
    Last edited by kavindra; 01-10-2013 at 05:09 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1