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
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 - Sep 18, 2015 at 07:56 PM
Related:
- Run macro when cell value changes
- How to run macro when data entered in a cell - Guide
- If cell contains specific text then return value in another cell ✓ - Excel Forum
- Spell number in excel without macro - Guide
- Automatically run a macro when opening a worksheet - Guide
- Excel macro to create new sheet based on value in cells - Guide
2 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Sep 17, 2015 at 06:48 AM
Sep 17, 2015 at 06:48 AM
Hello Branok,
Try the following slightly amended code:-
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.
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.
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
26
Sep 18, 2015 at 07:56 PM
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.
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
Sep 17, 2015 at 09:21 AM
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
Sep 17, 2015 at 09:27 AM