Select correct sheet when selecting cell

[Morre]

Hi

I have 52 week sheets in my workbook that are hidden. When a user doubble clicks on a cell the sheet of the current week should be displayed

thnx

 

[JamesW]

Hi,

Try this (if you double click on cell A1 then show the sheet of todays week number):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Target.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("A1")) Is Nothing Then
            weekNum = Trim(Str(Int(((Date - DateSerial(Year(Date), 1, 0)) + 6) / 7)))
            Sheets(weekNum).Visible = True
            Sheets(weekNum).Select
        End If
End Sub

 

[Morre]

where do i have to place this code? is it in a macro or in a sheet or in the workbook?

hope it works thnx

 

[JamesW]

Hi,

Press Alt+F11 on your main sheet, double click on the sheet in the Project Explorer on the left, paste it in and Bob's your mothers brother.