Results 1 to 3 of 3

Thread: Convert Private Sub, Double Click, to Sub

  1. #1

    Convert Private Sub, Double Click, to Sub



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I came upon this nifty script that hides / unhides groups of empty Rows, which is triggered when double clicked on a cell.

    Is it possible to modify this such that it no longer triggers via double click, but from the Active Cell, initiated by a macro?

    I will be assigning this to a button and would need the code to be a Sub event , instead of Private Sub.. (I think?)


    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim f As Long
    i = Target.End(xlDown).Row - 1
    If Target.Column = 1 Then
        Cancel = True
        If Range("A" & Target.Row + 1).Rows.Hidden Then
            For i = Target.Row + 1 To 65536
                If Range("A" & i).Rows.Hidden = False Then
                Exit For
                End If
            Next i
        Range("A" & Target.Row & ":A" & i).Rows.Hidden = False
        Else
            Range("A" & Target.Row + 1 & ":A" & i).Rows.Hidden = True
        End If
    End If
    End Sub

    Any help would be greatly appreciated!

    Thank you

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Stephen,

    This is untested, but I believe it should work for you:

    Code:
    Public Sub ToggleRowVis()
    Dim i As Long
    With Selection
        i = .End(xlDown).Row - 1
        If .Column = 1 Then
            Cancel = True
            If Range("A" & .Row + 1).Rows.Hidden Then
                For i = .Row + 1 To 65536
                    If Range("A" & i).Rows.Hidden = False Then
                    Exit For
                    End If
                Next i
            Range("A" & .Row & ":A" & i).Rows.Hidden = False
            Else
                Range("A" & .Row + 1 & ":A" & i).Rows.Hidden = True
            End If
        End If
    End With
    End Sub
    You'd want to throw that in a standard module.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Thanks, Ken

    Worked great!

    Thank you very, very much!

    J

Posting Permissions

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