vba worksheet_change event cannot assign sheetname

Question

I want to assign sheetname (Sh) and Column range (Target) for Workbook event, when in a worksheet "main" in column B value changes run the macro Next()

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Sh = "main"
   Target = Sh.Range("B:B")
   Next()
End Sub

gives me an error on Sh="main" Is it like, it will run on any cell in any column change occurs? But I need explicitly "main" sheet, column B (any cell) change event driven macro? Is it even possible in VBA?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Application.EnableEvents=False
   Set Sh = Worksheets("main")
   Set Target = Sh.Range("B:B")
   Next()
   Application.EnableEvents=True
End Sub

When change any cell in main Sheet subprocedure named Next runs. But, I need only changes in cells in B column when occurs only then Next subprocedure to run

To receive a proper answer, you would want to share the code of the `Next` procedure and/or describe where and what it does. If this code should just work on worksheet `main`, you should use the Worksheet Change event instead. — VBasic2008, Dec 27 '22 at 23:40
yes, my first mistake I put it on ThisWorkbook. Now will change to Worksheet main. But what about only events in column B:B? — xlmaster, Dec 27 '22 at 23:52
`If StrComp(sh.Name, "main", vbTextCompare) <> 0 Then Exit Sub: If Intersect(Target, sh.Range("B:B")) Is Nothing Then Exit Sub: App...=False: Next(): App...=True`. But you would want to add error handling to ensure that events get enabled at all cost. — VBasic2008, Dec 27 '22 at 23:54

score 2 · Accepted Answer · answered Dec 28 '22 at 07:51

Avoid the use of Next as a procedure name.
You can use Sh.Name to check if the change happened in the relevant worksheet.
While working with Workbook_SheetChange, Worksheet_Change or similar where you are switching off events, it is advisable to introduce error handling. I have spoken about this in Why MS Excel crashes and closes during Worksheet_Change Sub procedure?

Is this what you are trying?

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
   
    On Error GoTo Whoa
   
    If UCase(Trim(Sh.Name)) = "MAIN" Then
        If Not Intersect(Target, Sh.Columns(2)) Is Nothing Then
            '~~> Change this to the relevant procedure name
            MyProcedureName
        End If
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Sub MyProcedureName()
    MsgBox "Hello World"
End Sub

Another way which also does the same thing

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
        
    On Error GoTo Whoa
       
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Main")
   
    If Sh.Name = ws.Name Then
        If Not Intersect(Target, ws.Columns(2)) Is Nothing Then
            '~~> Change this to the relevant procedure name
            MyProcedureName
        End If
    End If
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Sub MyProcedureName()
    MsgBox "Hello World"
End Sub

vba worksheet_change event cannot assign sheetname

1 Answers1