Call Macro on a specific sheet / Excel

Question

I want to run a macro on a specific sheet, in my case the sheet is called "Tablet".

If a cell value in "Tabelle1" changes, I want to run this macro in the "Tablet" sheet.

Code in my Tabelle1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$2" Then
        Call Delete_OptionB1
    End If
End Sub

This part works.

Macro Code:

Sub Delete_OptionB1()
'
' Delete_OptionB1 Makro
'
     With Worksheets("Tablet")
    .Range("M2:AD2").Select
     Selection.ClearContents
    End With
End Sub

This wont do the job. Any suggestions how I get this working?

Mark Fitzgerald · Accepted Answer · 2018-12-11T13:49:36.560

2

In your code using a with block

 With Worksheets("Tablet")
.Range("M2:AD2").Select
 Selection.ClearContents
End With

You are selecting .Range("M2:AD2").Select but then clearing the contents of the selection on whatever sheet may be active when you Delete_OptionB1. Change to include a . - .Selection.ClearContents.

Even better, get rid or the With...End With and Select altogether. A single line will do it all:

Sub Delete_OptionB2()
'
' Delete_OptionB1 Makro
'
    Worksheets("Tablet").Range("M2:AD2").ClearContents
End Sub

edited Dec 11 '18 at 13:49

answered Dec 11 '18 at 13:37

Mark Fitzgerald

3,048
3
24
29

@Mark Just a note: A `.Selection` does not exist in the `Worksheet` object. – Pᴇʜ Dec 11 '18 at 14:33

Pᴇʜ · Answer 2 · 2018-12-11T13:34:45.640

Instead of …

Target.Address = "$C$2"

… better use the Application.Intersect method to make it work if Target is more than one cell (this can happen when you copy/paste a range):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Parent.Range("C2")) Is Nothing Then
        Delete_OptionB1 'you don't need the Call statement
    End If
End Sub

If Delete_OptionB1 is not in a public module but in a workbook use eg Tablet.Delete_OptionB1

Make Delete_OptionB1 public, and avoid using .Select and Selection. (also see How to avoid using Select in Excel VBA)

Public Sub Delete_OptionB1() 'make it public
    ThisWorkbook.Worksheets("Tablet").Range("M2:AD2").ClearContents
End Sub

score 0 · Answer 3 · answered Dec 11 '18 at 13:50

Place this in the Tabelle1 worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("$C$2")) Is Nothing Then
        Application.EnableEvents = False
            Call Delete_OptionB1
        Application.EnableEvents = True
    End If
End Sub

Place this in a standard module:

Sub Delete_OptionB1()
'
' Delete_OptionB1 Makro
'
     With Worksheets("Tablet")
        .Range("M2:AD2").ClearContents
    End With
End Sub

Call Macro on a specific sheet / Excel

3 Answers3