CODEWITHSUNDEEP
excelvba
GCode
GCode

Reputation: 21

Referencing a cell from another worksheet in a function for VBA

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.

Upvotes: 1

Views: 1406

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

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

Upvotes: 3

VBasic2008
VBasic2008

Reputation: 55073

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.

Upvotes: 0

Related Questions