CODEWITHSUNDEEP
vbaexcel
Andy
Andy

Reputation: 1432

How to execute Macro code inside a module from a worksheet_change event

I have a set of raw data on sheet 1 of my workbook. On sheet 2 I use formulas to pull in some of that data from sheet 1.

Using a Macro that I created and posted in Module 1 I want to hide any rows that do not contain specific data. I am able to execute the macro directly when I need to via Run>Run Sub/Userform. It works perfectly.

However, I would prefer it to run when it needs to update via a worksheet_change event in the background whenever an edit is made to sheet 1. Because I am making edits on sheet 1 but want the change to execute the macro on sheet 2 I understand that the worksheet_change event has to be placed in "This Worksheet" as opposed to the specific sheet.

Here's the macro code

Sub HideRows()
Dim i As Integer
i = 1
Do While Not Cells(i, 5) = ""
    If Cells(i, 5).Value = 0 Then
        Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = True
    ElseIf Cells(i, 5).Value <> 0 And Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = True Then
        Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = False
    End If
i = i + 1
Loop
End Sub

Run directly the code above does what I need. The code I am using below to execute this via a worksheet_change event doesn't work.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Me.Worksheets("Sheet2")
        Call HideRows
    End With
End Sub

Any help with how to execute the macro using worksheet_change would be appreciated.

Upvotes: 3

Views: 2627

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Few points worth noting

  1. The problem is that you are not fully qualifying the cells so when the HideRows macro is called, even though you have used With Me.Worksheets("Sheet2") it is still referring to the current sheet which is Sheet1. Fully qualify your range objects as shown below. Notice the Dots before them?

  2. If the changes are happening in Col A of Sheet1 then trap that else your macro will run for any change in Sheet1 thereby making your workbook slow.

  3. You do not need to keep the macro in a module. You can put the entire code in Sheet1 as shown below

  4. Also Rows(CStr(i) + ":" + CStr(i)) can be written as Rows(i)

  5. When working with rows in Excel it is always advisable to declare them as Long and not Integer. Post Excel2007 the number of rows have increased and the Integer variable may not be able to accommodate that.

Is this what you are trying? Put this code in Sheet code area of Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long, lRow As Long

    '~~> Check of the change is happening in Col 1
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        With Worksheets("Sheet2")
            lRow = .Range("E" & .Rows.Count).End(xlUp).Row

            For i = 1 To lRow
                If .Cells(i, 5).Value = 0 Then
                    .Rows(i).EntireRow.Hidden = True
                ElseIf .Cells(i, 5).Value <> 0 And .Rows(i).EntireRow.Hidden = True Then
                    .Rows(i).EntireRow.Hidden = False
                End If
            Next i
        End With
    End If
End Sub

Upvotes: 2

Related Questions