Consulting

Results 1 to 5 of 5

Thread: Show Hide ranges with button selection

  1. #1

    Show Hide ranges with button selection

    Hi, I need help with VBA. Please indulge me.

    My buttons called crse1, crse2, crse3, crse4 and crse5 are supposed to call their respective cases in the CallHideAndRevealCells() sub. In each of the cases, I set the value of the Range and pass that range into their respective functions depending on the amount of mods it is has. I tried asking chatGPT but its not working.

    My intent is for the cells to reveal themselves everytime I click the button and immediately go through the loop and hide all but the first line with an "n" in the cells.

    Please help.

    Cheers,
    ArmySanta

    Sub CallHideAndRevealCells()    ' Get the name of the button that was clicked
        Dim buttonName As String
        Dim rng As Range
        buttonName = Application.Caller
        
        ' Call the appropriate function based on the button name
        Select Case buttonName
    
    
            Case "crse1"
                Set rng = Range("C2:C21")
                Hide_And_Reveal_Courses_1_Mod rng
    
            Case "crse2"
                Set rng = Range("C49:C88")
                Hide_And_Reveal_Courses_2_Mods rng
    
            Case "crse3"
                Set rng = Range("C194:C253")
                Hide_And_Reveal_Courses_3_Mods rng
    
            Case "crse4"
                Set rng = Range("C666:C745")
                Hide_And_Reveal_Courses_4_Mods rng
    
    
            Case "crse5"
                Set rng = Range("C768:C867")
                Hide_And_Reveal_Courses_5_Mods rng
    
        End Select
    End Sub

    Sub Hide_And_Reveal_Courses_1_Mod(rng As Range)
    
        Dim ModOne1 As Range
        Dim ModOne2 As Range
        Dim ModOneRange As Range
        
        Set ModOneRange = rng
        
        rng.EntireRow.Hidden = False
    
    
        'Loop through the cells in the module
        For Each ModOne1 In ModOneRange.Cells
            'If the cell is "n", hide the next set of the course
            If ModOne1.Value = "n" Then
                For Each ModOne2 In Range(ModOne1.Offset(2, 0), ModOneRange.Cells(ModOneRange.Cells.Count)).Cells
                    If ModOne2.Value = "n" Then ModOne2.EntireRow.Hidden = True
                Next ModOne2
            End If
        Next ModOne1
    End Sub
    
    
    
    
    
    
    
    
    Sub Hide_And_Reveal_Courses_2_Mods(rng As Range)
        
        ' Declare variables to hold the different ranges we'll be working with
        Dim ModOne1 As Range
        Dim ModOne2 As Range
        Dim ModTwo1 As Range
        Dim ModTwo2 As Range
        Dim ModOneRange As Range
        Dim ModTwoRange As Range
        
        ' Set ModOneRange to the range passed into the sub
        Set ModOneRange = rng
        
        ' Set ModTwoRange to be the range directly below ModOneRange
        Set ModTwoRange = ModOneRange.Offset(1, 0)
    
    
        ' Unhide all rows in the range passed into the sub
        rng.EntireRow.Hidden = False
    
    
        ' Loop through the cells in the first mod (ModOneRange)
        For Each ModOne1 In ModOneRange.Cells
            
            ' If the value of the cell is "n", we need to hide the next set of rows
            If ModOne1.Value = "n" Then
                
                ' Loop through the cells in the range starting from the cell two rows below ModOne1
                ' and going until the end of ModOneRange (the last row in the range passed into the sub)
                For Each ModOne2 In Range(ModOne1.Offset(2, 0), ModOneRange.Cells(ModOneRange.Rows.Count, 1)).Cells
                    
                    ' If the value of the cell is "n", hide the entire row
                    If ModOne2.Value = "n" Then ModOne2.EntireRow.Hidden = True
                
                Next ModOne2
                
            End If
            
        Next ModOne1
        
        ' Loop through the cells in the second mod (ModTwoRange)
        For Each ModTwo1 In ModTwoRange.Cells
            
            ' If the value of the cell is "n", we need to hide the next set of rows
            If ModTwo1.Value = "n" Then
                
                ' Loop through the cells in the range starting from the cell two rows below ModTwo1
                ' and going until the last row in ModTwoRange
                For Each ModTwo2 In Range(ModTwo1.Offset(2, 0), ModTwoRange.Cells(ModTwoRange.Rows.Count, 1)).Cells
                    
                    ' If the value of the cell is "n", hide the entire row
                    If ModTwo2.Value = "n" Then ModTwo2.EntireRow.Hidden = True
                
                Next ModTwo2
                
            End If
            
        Next ModTwo1
        
    End Sub
    
    
    Sub Hide_And_Reveal_Courses_3_Mods(rng As Range)
    
    
        Dim ModOne1 As Range
        Dim ModOne2 As Range
        Dim ModTwo1 As Range
        Dim ModTwo2 As Range
        Dim ModThree1 As Range
        Dim ModThree2 As Range
        
        Dim ModOneRange As Range
        Dim ModTwoRange As Range
        Dim ModThreeRange As Range
        
        Set ModOneRange = rng
        Set ModTwoRange = ModOneRange.Offset(1, 0)
        Set ModThreeRange = ModOneRange.Offset(2, 0)
    
    
        rng.EntireRow.Hidden = False
    
    
        'Loop through the cells in the first module
        For Each ModOne1 In ModOneRange.Cells
            'If the cell is "n", hide the next set of the course
            If ModOne1.Value = "n" Then
                For Each ModOne2 In Range(ModOne1.Offset(2, 0), ModOneRange.Cells.Count).Cells
                    If ModOne2.Value = "n" Then ModOne2.EntireRow.Hidden = True
                Next ModOne2
            End If
        Next ModOne1
        
        'Loop through the cells in the second module
        For Each ModTwo1 In ModTwoRange.Cells
            'If the cell is "n", hide the next set of the course
            If ModTwo1.Value = "n" Then
                For Each ModTwo2 In Range(ModTwo1.Offset(2, 0), ModTwoRange.Cells.Count).Cells
                    If ModTwo2.Value = "n" Then ModTwo2.EntireRow.Hidden = True
                Next ModTwo2
            End If
        Next ModTwo1
        
        'Loop through the cells in the third module
        For Each ModThree1 In ModThreeRange.Cells
            'If the cell is "n", hide the next set of the course
            If ModThree1.Value = "n" Then
                For Each ModThree2 In Range(ModThree1.Offset(2, 0), ModThreeRange.Cells.Count).Cells
                    If ModThree2.Value = "n" Then ModThree2.EntireRow.Hidden = True
                Next ModThree2
            End If
        Next ModThree1
    End Sub
    
    
    
    
    Sub Hide_And_Reveal_Courses_4_Mods(rng As Range)
    
    
        Dim ModOne1 As Range
        Dim ModOne2 As Range
        Dim ModTwo1 As Range
        Dim ModTwo2 As Range
        Dim ModThree1 As Range
        Dim ModThree2 As Range
        Dim ModFour1 As Range
        Dim ModFour2 As Range
    
    
        Dim ModOneRange As Range
        Dim ModTwoRange As Range
        Dim ModThreeRange As Range
        Dim ModFourRange As Range
        
        Set ModOneRange = rng
        Set ModTwoRange = ModOneRange.Offset(1, 0)
        Set ModThreeRange = ModOneRange.Offset(2, 0)
        Set ModFourRange = ModOneRange.Offset(3, 0)
    
    
        rng.EntireRow.Hidden = False
    
    
        'Loop through the cells in the first mod
        For Each ModOne1 In ModOneRange.Cells
            'If the cell is "n", hide the next set of the course
            If ModOne1.Value = "n" Then
                For Each ModOne2 In Range(ModOne1.Offset(2, 0), ModOneRange.Cells(ModOne1.Cells.Count)).Cells
                    If ModOne2.Value = "n" Then ModOne2.EntireRow.Hidden = True
                Next ModOne2
            End If
        Next ModOne1
        
        'Loop through the cells in the second mod
        For Each ModTwo1 In ModTwoRange.Cells
            'If the cell is "n", hide the next set of the course
            If ModTwo1.Value = "n" Then
                For Each ModTwo2 In Range(ModTwo1.Offset(2, 0), ModTwoRange.Cells(ModTwo1.Cells.Count)).Cells
                    If ModTwo2.Value = "n" Then ModTwo2.EntireRow.Hidden = True
                Next ModTwo2
            End If
        Next ModTwo1
        
        'Loop through the cells in the third mod
        For Each ModThree1 In ModThreeRange.Cells
            'If the cell is "n", hide the next set of the course
            If ModThree1.Value = "n" Then
                For Each ModThree2 In Range(ModThree1.Offset(2, 0), ModThreeRange.Cells(ModThree1.Cells.Count)).Cells
                    If ModThree2.Value = "n" Then ModThree2.EntireRow.Hidden = True
                Next ModThree2
            End If
        Next ModThree1
        
        'Loop through the cells in the fourth mod
        For Each ModFour1 In ModFourRange.Cells
            'If the cell is "n", hide the next set of the course
            If ModFour1.Value = "n" Then
                For Each ModFour2 In Range(ModFour1.Offset(2, 0), ModFourRange.Cells(ModFour1.Cells.Count)).Cells
                    If ModFour2.Value = "n" Then ModFour2.EntireRow.Hidden = True
                Next ModFour2
            End If
        Next ModFour1
    
    
    End Sub
    
    
    Sub Hide_And_Reveal_Courses_5_Mods(rng As Range)
    
    
        Dim ModOne1 As Range
        Dim ModOne2 As Range
        Dim ModTwo1 As Range
        Dim ModTwo2 As Range
        Dim ModThree1 As Range
        Dim ModThree2 As Range
        Dim ModFour1 As Range
        Dim ModFour2 As Range
        Dim ModFive1 As Range
        Dim ModFive2 As Range
    
    
        Dim ModOneRange As Range
        Dim ModTwoRange As Range
        Dim ModThreeRange As Range
        Dim ModFourRange As Range
        Dim ModFiveRange As Range
        
        Set ModOneRange = rng
        Set ModTwoRange = ModOneRange.Offset(1, 0)
        Set ModThreeRange = ModTwoRange.Offset(1, 0)
        Set ModFourRange = ModThreeRange.Offset(1, 0)
        Set ModFiveRange = ModFourRange.Offset(1, 0)
    
    
        rng.EntireRow.Hidden = False
    
    
        'Loop through the cells in the first mod
        For Each ModOne1 In ModOneRange.Cells
            'If the cell is "n", hide the next set of the course
            If ModOne1.Value = "n" Then
                For Each ModOne2 In Range(ModOne1.Offset(2, 0), ModOneRange.Cells(ModOne1.Cells.Count)).Cells
                    If ModOne2.Value = "n" Then ModOne2.EntireRow.Hidden = True
                Next ModOne2
            End If
        Next ModOne1
        
        'Loop through the cells in the second mod
        For Each ModTwo1 In ModTwoRange.Cells
            'If the cell is "n", hide the next set of the course
            If ModTwo1.Value = "n" Then
                For Each ModTwo2 In Range(ModTwo1.Offset(2, 0), ModTwoRange.Cells(ModTwo1.Cells.Count)).Cells
                    If ModTwo2.Value = "n" Then ModTwo2.EntireRow.Hidden = True
                Next ModTwo2
            End If
        Next ModTwo1
        
        'Loop through the cells in the third mod
        For Each ModThree1 In ModThreeRange.Cells
            'If the cell is "n", hide the next set of the course
            If ModThree1.Value = "n" Then
                For Each ModThree2 In Range(ModThree1.Offset(2, 0), ModThreeRange.Cells(ModThree1.Cells.Count)).Cells
                    If ModThree2.Value = "n" Then ModThree2.EntireRow.Hidden = True
                Next ModThree2
            End If
        Next ModThree1
        
        'Loop through the cells in the fourth mod
        For Each ModFour1 In ModFourRange.Cells
            'If the cell is "n", hide the next set of the course
            If ModFour1.Value = "n" Then
                For Each ModFour2 In Range(ModFour1.Offset(2, 0), ModFourRange.Cells(ModFour1.Cells.Count)).Cells
                    If ModFour2.Value = "n" Then ModFour2.EntireRow.Hidden = True
                Next ModFour2
            End If
        Next ModFour1
        
        'Loop through the cells in the fifth mod
        For Each ModFive1 In ModFiveRange.Cells
        'If the cell is "n", hide the next set of the course
        If ModFive1.Value = "n" Then
            For Each ModFive2 In Range(ModFive1.Offset(2, 0), ModFiveRange.Cells(ModFive1.Cells.Count)).Cells
                If ModFive2.Value = "n" Then ModFive2.EntireRow.Hidden = True
            Next ModFive2
        End If
    Next ModFive1
    End Sub

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    1. A more descriptive title will usually get more members to take a look, after all most of us are already here to "Please Help"

    2. That's a LOT of code and seems to be dependent on worksheet data, so it'd be much easier if you attached a workbook with the macros and enough data to show the issue. Alternative, you can just make a small workbook with only the specific issue (i.e. not the entire project)
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    So you're just trying to hide all the cells in those specified range, upon its relative button click, which contain a value of "n"? If so, you can just use something like this.

    Sub CallHideAndRevealCells()
    
        Dim CourseRange1 As Range
        Dim CourseRange2 As Range
        Dim CourseRange3 As Range
        Dim CourseRange4 As Range
        Dim CourseRange5 As Range
        Dim CourseButtonName As String
        
        CourseButtonName = Application.Caller
        Set CourseRange1 = ActiveSheet.Range("C2:C21")
        Set CourseRange2 = ActiveSheet.Range("C49:C88")
        Set CourseRange3 = ActiveSheet.Range("C194:C253")
        Set CourseRange4 = ActiveSheet.Range("C666:C745")
        Set CourseRange5 = ActiveSheet.Range("C768:C867")
        
        Select Case CourseButtonName
        Case "crse1": ShowCourses CourseRange1
        Case "crse2": ShowCourses CourseRange2
        Case "crse3": ShowCourses CourseRange3
        Case "crse4": ShowCourses CourseRange4
        Case "crse5": ShowCourses CourseRange5
        End Select
        
    End Sub
    
    
    Sub ShowCourses( _
        ByVal CourseRange As Range, _
        Optional ByVal MatchingValue As String = "n" _
        )
        
        Dim Cell As Range
        Dim FoundCells As Range
        
        CourseRange.EntireRow.Hidden = False
        
        For Each Cell In CourseRange
            If Cell.Value = MatchingValue Then
                If FoundCells Is Nothing Then
                    Set FoundCells = Cell
                Else
                    Set FoundCells = Union(FoundCells, Cell)
                End If
            End If
        Next Cell
        
        If Not FoundCells Is Nothing Then
            FoundCells.EntireRow.Hidden = True
        End If
        
    End Sub

  4. #4
    Ok, I summarized the code because it is work related. That's why there is enormous gaps in the cell ranges.

    Ok so what I am trying to do is to hide all but 1 of the cells that has an "n".
    For the mod-type courses, I am trying to hide all but 1 of each the mods of the cells with an "n"
    If the user changes the value from an "n" to a "y" and clicks the button, then I want it to show the next value of "n".

    Does that make sense?

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by ArmySanta View Post
    Does that make sense?
    Which is essentially what I was alluding to in your previous thread.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

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