1

Very novice at VBA so please bear with me. Im trying to delete a table if a cell (B2) in another sheet changes.

Currently I have:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Worksheets("sheet2").Range("B2")) Is Nothing Then
    Range("B21:D30").ClearContents
End If
End Sub

I've tried many variations, indirect, and different syntax but none work.

Update: I should also mention that B2 on sheet2 will be changing based on the user selecting a group of radio buttons which are linked to sheet2!B2. I.e. I am not directly changing the value of B2 from sheet2. In fact sheet2 will be eventually hidden.

Improve this question
5
  • You are never going to Intersect a private Worksheet_Change's Target with a cell on a different worksheet. Target is the cell or cells on that worksheet that have changed so it is impossible to intersect with a cell on another worksheet. Commented Mar 15, 2019 at 1:09
  • Thanks for your comment! Is there any other way I can clear my table if a cell from another sheet changes? Commented Mar 15, 2019 at 1:14
  • I should also mention that B2 on sheet2 will be changing based on the user selecting a group of radio buttons which are linked to sheet2!B2. Write a code to handle the changing of radio buttons and in that do what you want Commented Mar 15, 2019 at 2:49
  • @SiddharthRout Yes that what I ended up doing. Thanks! Commented Mar 15, 2019 at 2:56
  • BTW you can trap changes to a hidden workbook from your current workbook. Posted an answer to demonstrate that :) Commented Mar 15, 2019 at 4:41

2 Answers 2

Reset to default
3

To trap the events in the Sheet2 of the hidden workbook (Let's call it Book2), you need to create a class to manage the _SheetChange event capture.

Let's say you want to capture the events in Book2.Sheet2 from Book1. Do this

1. Insert a class module (Let's call it Class1) and paste this code there

Code

Private WithEvents hiddenWb As Workbook

Public Property Set Workbook(wb As Workbook)
    Set hiddenWb = wb
End Property

Public Property Get Workbook() As Workbook
    Set Workbook = hiddenWb
End Property

Private Sub hiddenWb_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Sheet2" Then
        If Not Intersect(Target, Sh.Range("B2")) Is Nothing Then
            MsgBox "Range B2 was chnaged"
        End If
    End If
End Sub

2. In a module paste this code

Code

Option Explicit

Dim cWb As New Class1

Sub Sample()
    '~~> Set a reference to the hidden workbook
    Set cWb.Workbook = Workbooks("Book2")

    '~~> Change the value of the cell B2
    cWb.Workbook.Sheets("Sheet2").Cells(2, 2).Value = "Blah Blah"
End Sub

Screenshots

enter image description here

Testing

Run the procedure Sample() from Book1

Intresting Read

Events And Event Procedures In VBA by Charles H. Pearson

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Points for referencing chip
0

This has to be written in the Sheet2 module:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Me
        If Not Intersect(Target, .Range("B2")) Is Nothing Then
            .Parent.Worksheets("Sheet1").Range("B21:D30").ClearContents
        End If
    End with
End Sub

Change "Sheet1" to fit your worksheet name.

Improve this answer

7 Comments

Thank you for your response! This works however im actually changing the value of B2 from a radio button on sheet1 (not directly changing B2 from sheet2), And for some reason this does not trigger the ClearContent event. Is there a way around this?
@user11206088: I think I'll have to see the Option Button Code. It is working fine on my machine. I've added the Option Button and linked cell B2 to it and added Range("B2") = "Change" to the code. And on every click, the range is being deleted.
The only code I see is: Public Sub AllowMacros() Me.Protect UserInterfaceOnly:=True End Sub However there is a user interface options under format control when you right click the button, that allows you to link to cell. Could you show me your workbook?
BTW this will not work if you have a formula in B2. Then you have to use the Worksheet Calculate event. But I guess you would have mentioned if there was a formula in B2. It will work when B2 is changed manually or via VBA.
I dont believe B2 has a formula. It is just being changed by radio buttons provided by the "form controls" package.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.