Auto-running macro when cell is changed.

Closed
Branok Posts 7 Registration date Friday September 11, 2015 Status Member Last seen October 9, 2015 - Sep 17, 2015 at 04:21 AM
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 - Sep 18, 2015 at 07:56 PM
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

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Sep 17, 2015 at 06:48 AM
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
Branok Posts 7 Registration date Friday September 11, 2015 Status Member Last seen October 9, 2015
Sep 17, 2015 at 09:21 AM
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
0
Branok Posts 7 Registration date Friday September 11, 2015 Status Member Last seen October 9, 2015
Sep 17, 2015 at 09:27 AM
Moreover, I forgot to mention that cells in ("ControlSheet") Column B will be all locked and secured after I'll finish this document.
0
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 25
Sep 18, 2015 at 07:56 PM
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
0