Auto-running macro when cell is changed.

Closed
Report
Posts
7
Registration date
Friday September 11, 2015
Status
Member
Last seen
October 9, 2015
-
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
-
Hello guys,
I am using simple macro for one sheet (ControlSheet) to hide rows, in which certain cell (B11;B51) contains text "Corrective Action not available". Macro should also unhide them if text inside those cells change to whatever else.

'Sub HURows()
BeginRow = 11
EndRow = 51
ChkCol = 2

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "Corrective Action not available" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If

Next RowCnt
End Sub


Please, how could I make this macro work automatically, after text in cells change.
Text is changing automatically as an output from other sheets.

Thank you in advance :)


2 replies

Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
Hello Branok,

Try the following slightly amended code:-

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub

BeginRow = 11
EndRow = 51
ChkCol = 2

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "Corrective Action not available" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If

Next RowCnt

Application.ScreenUpdating = True

End Sub


It is a Worksheet_Change event so everytime that you make an entry in Column B and click away or use the arrow keys, the code will execute and the relevant rows will be hidden.

Lines 3 and 20 in the code will stop screen flickering as the code executes.
Line 5 in the code restricts any changes to Column B otherwise the code will execute wherever on the sheet you click.

To implement the code, right click on the Control Sheet tab and select "view code". In the big white field that appears, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
7
Registration date
Friday September 11, 2015
Status
Member
Last seen
October 9, 2015

Thank you :)
But there is one glitch, I do not make any adjustments/changes in Column B in "ControlSheet" but there is another sheet in document ("D05"), from which text is refereed into Cells in Column B in "ControlSheet" sheet. Therefore it again do not work properly. I found it working after double-clicking and using arrow afterwards, on cell in Column B ("ControlSheet") after I made change in sheet "D05" sheet. This will be helpfull, but it is not me who will use document, so I need it work automatically.

Is there any possibility to add something to sheet where are changes actually performed ("D05")? Since ("ControlSheet") is just reference sheet. Kind of story-board...

Thank you in advance
Posts
7
Registration date
Friday September 11, 2015
Status
Member
Last seen
October 9, 2015

Moreover, I forgot to mention that cells in ("ControlSheet") Column B will be all locked and secured after I'll finish this document.
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
Try this version. Only a minor modification needed to be made to vcoolio's original code.

Paste this VBA into the D5 sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub

BeginRow = 5
EndRow = 14
ChkCol = 4

For RowCnt = BeginRow To EndRow
  newrow = (RowCnt * 2) + 1 ' calculate the row on the control sheet.
    If Cells(RowCnt, ChkCol).Value = "Corrective Action not available" Then
        Worksheets("ControlSheet").Cells(newrow, ChkCol).EntireRow.Hidden = True
    Else
        Worksheets("ControlSheet").Cells(newrow, ChkCol).EntireRow.Hidden = False
    End If

Next RowCnt

Application.ScreenUpdating = True

End Sub