CODEWITHSUNDEEP
vbaexcel
Cr1kk0
Cr1kk0

Reputation: 11

Applying Workbook_SheetChange to one worksheet

I found the below code I'm trying to adapt, however it seems that it runs on every worksheet within my workbook, and I only want it to apply to "Sheet3". I've tried pasting it into the Sheet3 code module, but that didn't seem to work.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
  ByVal Target As Excel.Range)
    Dim TimeStr As String

    On Error GoTo EndMacro
    If Application.Intersect(Target, Range("A1:a15")) Is Nothing Then
        Exit Sub
    End If
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    If Target.Value = "" Then
        Exit Sub
    End If

    Application.EnableEvents = False
    With Target
        If .HasFormula = False Then
            Select Case Len(.Value)
                Case 1 ' e.g., 1 = 00:01 AM
                    TimeStr = "00:0" & .Value
                Case 2 ' e.g., 12 = 00:12 AM
                    TimeStr = "00:" & .Value
                Case 3 ' e.g., 735 = 7:35 AM
                    TimeStr = Left(.Value, 1) & ":" & _
                    Right(.Value, 2)
                Case 4 ' e.g., 1234 = 12:34
                    TimeStr = Left(.Value, 2) & ":" & _
                    Right(.Value, 2)
                Case Else
                    Err.Raise 0
            End Select
            .Value = Format(TimeValue(TimeStr), "hh:mm")
        End If
    End With
    Application.EnableEvents = True

    Exit Sub

EndMacro:
    MsgBox "You did not enter a valid time"
    Application.EnableEvents = True
    ActiveCell.Offset(-1, 0).Select
End Sub

Where am I going wrong?

Upvotes: 1

Views: 2084

Answers (1)

user4039065
user4039065

Reputation:

You have confused the Workbook_SheetChange event macro with individual Worksheet_Change event macros.

You could isolate the existing Workbook_SheetChange by wrapping the code in something like this.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    If sh.Name = "Sheet3" Then
        'all of the rest of the code
    End If
End Sub

Alternately, put the code into the Sheet3 worksheet code page and change the sub to this.

Private Sub Worksheet_Change(ByVal Target As Range)
    'all of the rest of the code
End Sub

Upvotes: 1

Related Questions