VBA Module with checkboxes to use in excelClosed

Question

Dears,

I will be very grateful if some one can help me in the following.
I have excel file include 50 sheets for customers, in each sheet I have 2 checkboxes that if clicked (True) it will hide some rows. All the Checkboxes have the same names/numbers. So what I need is to make Module to use for all the sheets instead of using the following VBA code in each sheet.

Private Sub CheckBox15_Click()
If CheckBox15 = False Then
Range("A52:W89").Select
Selection.EntireRow.Hidden = True

Else:
Range("A52:W89").Select
Selection.EntireRow.Hidden = False
End If
End Sub
*****************************

Private Sub CheckBox16_Click()
If CheckBox16 = False Then
Range("A90:W132").Select
Selection.EntireRow.Hidden = True

Else:
Range("A90:W132").Select
Selection.EntireRow.Hidden = False
End If
End Sub

*****************************

Many thanks in advance for any kind assistance.

RayH · Answer

in a module:

Sub CheckBox1_Click()
Select Case ActiveSheet.Shapes("Check Box 1").ControlFormat.Value
    Case -4146
        Range("A52:W89").Select
        Selection.EntireRow.Hidden = True
    Case 1
        Range("A52:W89").Select
        Selection.EntireRow.Hidden = False
End Select
End Sub
 
Sub CheckBox2_Click()
Select Case ActiveSheet.Shapes("Check Box 2").ControlFormat.Value
Case -4146
    Range("A90:W132").Select
    Selection.EntireRow.Hidden = True
Case 1
    Range("A90:W132").Select
    Selection.EntireRow.Hidden = False
End Select
End Sub

On each of the checkboxes in each sheet Assign the appropriate macro.

This is assuming you are using the 'Form' checkbox and not the ActiveX Checkbox, in which case the code would work.

VBA Module with checkboxes to use in excel

1 response

Similar discussions

Subscribe To Our Newsletter!