vba worksheet_change event cannot assign sheetname-CodePudding

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

CodePudding user response：

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