CODEWITHSUNDEEP
excelvbaexcel-2010
dOPELELE
dOPELELE

Reputation: 33

Call Macro on a specific sheet / Excel

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?

Upvotes: 3

Views: 8836

Answers (3)

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 0

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

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

Upvotes: 2

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 0

Related Questions