vba help - hide/unhide sheets as per worksheet checkbox click

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

Need your help, I have 4 checkboxes in column E.
if user select whichever checkboxes corrensponding sheets needs to get hide or unhide.
check = unhide
uncheck = hide

please suggest how to achieve this task. suggest vba code or event. thanks in advance !

Tasks.xlsm
ABCDE
1SheetnamesHide/Unhide
2Sheet1
3sheet2
4sheet3
5sheet4
6
Master



attached image of excel.

1645608982544.png



I have added checkboxes via below code.

VBA Code:
Sub Addcheckboxes()
 
'Declare variables and data types
Dim cell, LRow As Single
Dim chkbx As CheckBox
Dim CLeft, CTop, CHeight, CWidth As Double
 
'Don't refresh or update screen while processing macro, this will make the macro quicker.
Application.ScreenUpdating = False
 
 
'Iterate through 2 to last non empty cell
For cell = 2 To 5
    
    'Check if cell in column A is not equal to nothing
    If Cells(cell, "A").Value = "" Then
 
        'Save cell dimensions and coordinates of corresponding cell in column E to variables
        CLeft = Cells(cell, "E").Left
        CTop = Cells(cell, "E").Top
        CHeight = Cells(cell, "E").Height
        CWidth = Cells(cell, "E").Width
 
        'Create checkbox based on dimension and coordinates data from variables
        ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
        End With
    End If
Next cell
 
'Turn on screen refresh
Application.ScreenUpdating = True
 
End Sub




Thanks
mg
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Try this code.
VBA Code:
Public Sub Add_Checkboxes()

    Dim MasterWs As Worksheet, ws As Worksheet
    Dim cb As CheckBox
    Dim r As Long

    Application.ScreenUpdating = False
    
    With ActiveWorkbook
    
        Set MasterWs = .Worksheets("Master")

        Delete_Checkboxes MasterWs

        r = 1
        For Each ws In .Worksheets
            If Not ws Is MasterWs Then
                ws.Visible = xlSheetVisible
                r = r + 1
                With MasterWs
                    .Cells(r, "D").Value = ws.Name
                    Set cb = .CheckBoxes.Add(.Cells(r, "E").Left, .Cells(r, "E").Top, .Cells(r, "E").Width, .Cells(r, "E").Height)
                End With
                With cb
                    .Caption = ""
                    .Value = xlOn
                    .Display3DShading = False
                    .Name = "CB_" & r
                    .OnAction = "CheckBox_Click"
                End With
            End If
        Next
        
    End With

    Application.ScreenUpdating = True

End Sub


Public Sub CheckBox_Click()

    Dim cb As CheckBox
    
    'Application.Caller is name of Checkbox that was clicked
    
    With ActiveSheet
        Set cb = .CheckBoxes(Application.Caller)
        Worksheets(.Cells(cb.TopLeftCell.Row, "D").Value).Visible = (cb.Value = xlOn)
    End With
    
End Sub


Private Sub Delete_Checkboxes(ws As Worksheet)
    With ws.CheckBoxes
        While .count > 0
            .Item(1).Delete
        Wend
    End With
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top