Modify code

[noelmus]

[FONT=&quot]I have this code (below) that works for worksheet "CHIT". I want to modify this code so that it works for another worksheet "CHIT2".[/FONT]
[FONT=&quot]So if I double click on worksheet "CHIT", it also works on worksheet "CHIT2".[/FONT]
[FONT=&quot]Any help is appreciated[/FONT]
[FONT=&quot]CODE:[/FONT]
[FONT=&quot]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Dim c As Range, a[/FONT]
[FONT=&quot] If Target.Address = "$K$8" Then[/FONT]
[FONT=&quot] ' Don't edit the cell[/FONT]
[FONT=&quot] Cancel = True[/FONT]
[FONT=&quot] ' Increment the invoice nunber[/FONT]
[FONT=&quot] Set c = Worksheets("CHIT").Range("J8")[/FONT]
[FONT=&quot] c.NumberFormat = "@"[/FONT]
[FONT=&quot] If InStr(c, "/") = 0 Then[/FONT]
[FONT=&quot] c = "000001/" & Format(Date, "yy")[/FONT]
[FONT=&quot] Else[/FONT]
[FONT=&quot] a = Split(c, "/")[/FONT]
[FONT=&quot] a(0) = Format(a(0) + 1, "00000#")[/FONT]
[FONT=&quot] If Format(a(1), "00") <> Format(Date, "yy") Then a(1) = Format(Date, "yy")[/FONT]
[FONT=&quot] c = Join(a, "/")[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]End Sub[/FONT]

 

[DanteAmor]

Remove the event from your "CHET" sheet and put the following event on ThisWorkbook

The event will work for the 2 sheets

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim c As Range, a
    Select Case LCase(Sh.Name)
        Case LCase("CHIT"), LCase("CHIT2")
            If Target.Address = "$K$8" Then
                ' Don't edit the cell
                Cancel = True
                ' Increment the invoice nunber
                Set c = Sh.Range("J8")
                c.NumberFormat = "@"
                If InStr(c, "/") = 0 Then
                    c = "000001/" & Format(Date, "yy")
                Else
                    a = Split(c, "/")
                    a(0) = Format(a(0) + 1, "00000#")
                    If Format(a(1), "00") <> Format(Date, "yy") Then a(1) = Format(Date, "yy")
                    c = Join(a, "/")
                End If
            End If
    End Select
End Sub

 

[noelmus]

Hi DanteAmor
Sorry if I don't give a good explanation. It works but I need that if I double click on any worksheet , it works for both. So the number will be the same for both worksheets.
If I double click on worksheet "CHIT", it counts 2 and worksheet "CHIT2" counts 1.

Thanks

 

[DanteAmor]

Try the following at the same Thisworkbook event

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim c As Range, a
    If Target.Address = "$K$8" Then
        ' Don't edit the cell
        Cancel = True
        ' Increment the invoice nunber
        
        Set c = Sheets("CHIT").Range("J8")
        c.NumberFormat = "@"
        If InStr(c, "/") = 0 Then
            c = "000001/" & Format(Date, "yy")
        Else
            a = Split(c, "/")
            a(0) = Format(a(0) + 1, "00000#")
            If Format(a(1), "00") <> Format(Date, "yy") Then a(1) = Format(Date, "yy")
            c = Join(a, "/")
        End If
        '
        Set c = Sheets("CHIT2").Range("J8")
        c.NumberFormat = "@"
        If InStr(c, "/") = 0 Then
            c = "000001/" & Format(Date, "yy")
        Else
            a = Split(c, "/")
            a(0) = Format(a(0) + 1, "00000#")
            If Format(a(1), "00") <> Format(Date, "yy") Then a(1) = Format(Date, "yy")
            c = Join(a, "/")
        End If
    
    End If
End Sub

 

[noelmus]

Hi DanteAmor,
Really appreciate your help.
Really big thanks

 

[DanteAmor]

I'm glad to help you