Hide rows that are relative to the checkbox clicked

liampog

Active Member
Joined
Aug 3, 2010
Messages
308
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi there

I'm successfully using the following code to hide rows that are fixed. However, I want to use this same macro for each day of the week where the row that is hidden is variable depending on which CheckBox is clicked. There are currently 7 checkboxes next to each of the days on the sheet that are positioned 6 rows ABOVE the 2 rows that need to be hidden.

Is there any way of doing this so that I only need one macro which "senses" where the checkbox is located and then hides the appropriate rows relative to the position of the checkbox. I can make 7 macros for each day, but that's not practical or efficient. It also means that should I need to insert/delete a row later, I'll need to change all 7 of the macros with the new row numbers.

Code:
Sub TournamentDay()


    Application.EnableEvents = False


    Application.ScreenUpdating = False


    ActiveSheet.Unprotect




        If Rows("15:16").Hidden = True Then


            Rows("15:16").Hidden = False


        Else


            Rows("15:16").Hidden = True


        End If


    With ActiveSheet
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        .EnableSelection = xlUnlockedCells
    End With


    Application.ScreenUpdating = True


    Application.EnableEvents = True


End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
I am assuming that each checkbox is linked to a cell on the sheet. In that case you can hide the row relative to this linked cell.
 
Upvote 0
Well what happens when you check a check box? what is linked to the checkbox? does the value of a cell change or is there some code linked to the checkbox?
 
Upvote 0
Well what happens when you check a check box? what is linked to the checkbox? does the value of a cell change or is there some code linked to the checkbox?

Hi there

It runs the code in my OP. However, everything is static (ie, the rows that get hidden). I want the checkbox to sense where it is on the sheet and hide the rows that are 6 and 7 rows below where the checkbox is located.

A dynamic macro rather than a static one for each checkbox.
 
Upvote 0
Can you post this code here please?
 
Upvote 0
Well yes there is but it is not really going to help you.

You can request the 'Left' coordinates of the control, which theoretically tells you where it is on the sheet, but it only tells you where it is on the window. So then you would have to do complicated things to tell you which position coincides with which column. But you have a bigger problem in that I don't know how you can determine which control (checkbox) called the macro.

So I would suggest each checkbox to have it's own very short macro, that then calls the main macro passing an ID, where the real stuff is done.
Code:
Sub ChkBox1()
    TournamentDay 1
End Sub
Sub ChkBox2()
    TournamentDay 2
End Sub
Sub ChkBox3()
    TournamentDay 3
End Sub






Sub TournamentDay(Cntrl As Integer)




    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Unprotect
    End With
    
    Select Case Cntrl
        Case 1
            With Rows("15:16")
                If .Hidden = True Then
                    .Hidden = False
                Else
                    .Hidden = True
                End If
            End With
        Case 2
            With Rows("17:18")
                If .Hidden = True Then
                    .Hidden = False
                Else
                    .Hidden = True
                End If
            End With
        Case 3
            With Rows("19:12")
                If .Hidden = True Then
                    .Hidden = False
                Else
                    .Hidden = True
                End If
            End With
        
    With ActiveSheet
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        .EnableSelection = xlUnlockedCells
    End With
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With




End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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