CODEWITHSUNDEEP
vbaexcel
Ibrahim
Ibrahim

Reputation: 79

Toggle Show/Hidden Sheet

I want to hide a sheet and only show it when i double click on a specific sheet. let's say i have sheet1 and sheet2. I want sheet2 to be always hidden until i double click on a cell (anywhere on the sheet1 not a specific cell) in sheet1 and hide it again when i leave it.Is it possible? I've read something about

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object,ByVal Target As Range, ByVal Cancel As Boolean)

but i haven't be able to use it. Thank You for Time and Help

Upvotes: 0

Views: 161

Answers (3)

geoff cahill
geoff cahill

Reputation: 31

I tested the following using Excel 2010 under Windows 7, ...paste the following into the "ThisWorkbook" code pane...hope this helps...

Dim strLastActive As String
Private Sub Worksheet_Activate()
    strLastActive = ActiveSheet.Name
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If ActiveSheet Is ThisWorkbook.Worksheets("Sheet1") Then
        If Not ThisWorkbook.Worksheets("Sheet2").Visible Then
            ThisWorkbook.Worksheets("Sheet2").Visible = True
        End If
    End If
End Sub
Private Sub Worksheet_Deactivate()
    If strLastActive = "Sheet2" Then
        ThisWorkbook.Worksheets("Sheet2").Visible = False
    End If
End Sub

Upvotes: 0

DisplayName
DisplayName

Reputation: 13386

place this in Sheet1 code pane:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Worksheets("Sheet2")
        .Visible = True
        .Activate
    End With
End Sub

and place this in sheet2 code pane:

Private Sub Worksheet_Deactivate()
    Me.Visible = False
End Sub

Upvotes: 1

SJR
SJR

Reputation: 23081

Perhaps this, both in the sheet1 module (right-click sheet tab, view code and paste code)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Cancel = True

Sheet2.Visible = xlSheetVisible

End Sub

Private Sub Worksheet_Deactivate()

Sheet2.Visible = xlSheetHidden

End Sub

Upvotes: 1

Related Questions