On Event Coding

[Mudbutt]

I have a dashboard worksheet, and I'm trying to figure out how to unhide and activate another worksheet within the workbook upon double clicking cell "C4".

Does anyone know how to do this? My Worksheet_Change and Target.Address code doesn't seem to be doing it.

 

[Hermanito]

try using this event instead:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'your code here
End Sub

I assume you already have the code to unhide and activate, just put that in the eventhandler shown above

 

[GlennUK]

Wouldn't the Worksheet_BeforeDoubleClick event be the one to use?

 

[VoG]

Try like this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address(False, False) = "C4" Then
    Cancel = True
    With Sheets("Sheet1")
        .Visible = xlSheetVisible
        .Select
    End With
End If
End Sub

 

[Mudbutt]

Thank you so much VoG, that worked perfectly. One question, what is the function of the "Cancel = True" portion?

 

[VoG]

By default double clicking a cell puts it into Edit mode. Setting Cancel to True exits Edit mode.

 

[Mudbutt]

Perfect! Thank you so much for your assistance.

 

[Mudbutt]

Hey, VoG:

I have a follow up question for you. I have a dashboard page with a bunch of totals on it and I'm creating this event code so that it gives specific detail of that item when it is double clicked. I didn't want to do this via pivot tables because the pivots display too much unnecessary data when drilling down so i basically made a page that links to all my pivots and I'm improvising.

However, to save space on this workbook, I think it would be most practical if everytime someone double-clicked it creates the sheet as new, and then fills in the data for the first time with formulas so that i don't have tons of worksheets just hidden with prebuilt formulas. With this logic, I was thinking that I would need some kind of delete sheet event code so that each time it's only creating one sheet and the other that was created before be deleted.

Would i do this with select case, and name the sheets that i don't want deleted in this file, and have it delete all the rest? The sheet names i need intact are "MasterList" & "Dashboard". I'm not really sure how to write this code, but thus far, this is what i have:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address(False, False) = "C4" Then
    Cancel = True
    Application.ScreenUpdating = False
    Sheets.Add.Name = "SunOp-FGSch"
    Sheets("SunOp-FGSch").Activate
    Sheets("SunOp-FGSch").Range("A1").FormulaR1C1 = "Materials"
    Sheets("SunOp-FGSch").Range("B1").FormulaR1C1 = "Description"
    Sheets("SunOp-FGSch").Range("C1").FormulaR1C1 = "FG Scheduled"
    Sheets("SunOp-FGSch").Range("A1:C1").Select
        With Selection.Font
            .ThemeColor = xlThemeColorDark1
            .Bold = True
            .Name = "Arial"
            .Size = 10
        End With
        With Selection.Interior
            .ThemeColor = xlThemeColorLight1
        End With
    Sheets("SunOp-FGSch").Range("A2:A5000").Formula = "=IF(AND(MasterList!E3=""Sun Optics"", MasterList!T3>0),MasterList!B3,"""")"
    Sheets("SunOp-FGSch").Range("A2:A" & lastRow).Sort _
        Key1:=Sheets("SunOp-FGSch").Columns("A"), Order:=xlDescending
    Application.ScreenUpdating = True
End If

 

[VoG]

This is a new question. Please start a new thread.

 

[Mudbutt]

Ok, just did. Thanks.