Run VBA Macro when cell value updates, but cell value is a formula that references another sheet

[carlsarlsburg]

I am new to using VBA in Excel so please bear with me any help is appreciated.

I am creating an excel sheet that users past in data from another program into sheet 3 (titled 651R-2 Data). Sheet 1 (titled Cover Sheet) references data on sheet using the vlookup formula and displays certain data to be displayed on a coversheet. I came up with the following code that hides certain rows based on a value on the Cover Sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Rows("1:100").EntireRow.Hidden = False
x = Range("D18").Value
Select Case x
Case 0: Rows("40:48").EntireRow.Hidden = True
Case 1: Rows("20:39").EntireRow.Hidden = True
End Select

End Sub

In Cell D18 the following formula is written, =VLOOKUP(C18,'651R-2 Data'!D$1:E$582,2,FALSE). On sheet 651R-2 Data, the value that it looks up only ranges from 0 to 1.

With Worksheet_SelectionChange whenever I click on another cell in Excel, the hidden rows flicker on and off, which is undesirable. It seems that if I change this to Worksheet_Change, changing the value that D18 looks up on sheet 651R-2 Data will not run the VBA macro to update which rows should be hidden rows (maybe this is because cell D18 is a referencing another sheet so the formula isn't changing so Worksheet_Change doesn't see this as a change even if it evaluates to a different value).

What can I do to update the hidden rows on the Cover sheet with my VBA macro, when the user pastes data into sheet 651R-2 that changes what cell D18 evaluates to without using Worksheet_SelectionChange which causes the rows to flicker?

Thank you!

 

[offthelip]

try moving it to the worksheet calculate event

 

[carlsarlsburg]

I changed my VBA code to. Private Sub Worksheet_Calculate(ByVal Target As Range)

When I change the value that D18 is looking up I get Compile error: Procedure declaration does not match description of even or procedure having the same name.

 

[Joe4]

I changed my VBA code to. Private Sub Worksheet_Calculate(ByVal Target As Range)

When I change the value that D18 is looking up I get Compile error: Procedure declaration does not match description of even or procedure having the same name.

Please post your current code that is returning this error.

 

[carlsarlsburg]

Private Sub Worksheet_Calculate(ByVal Target As Range)

Rows("1:100").EntireRow.Hidden = False
x = Range("D18").Value
Select Case x
Case 0: Rows("40:48").EntireRow.Hidden = True
Case 1: Rows("20:39").EntireRow.Hidden = True
End Select


End Sub

With my limited knowledge of VBA when offthelip said the following:

try moving it to the worksheet calculate event

They meant change

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

to

Private Sub Worksheet_Calculate(ByVal Target As Range)

Which resulted in a compile error.

 

[Joe4]

Worksheet_Calculate does not take in any arguments (parameters).
Just remove them from the first line.

Also, there is no reason to set the value of D18 equal to x, i.e.

Private Sub Worksheet_Calculate()

Rows("1:100").EntireRow.Hidden = False
Select Case Range("D18").Value
    Case 0: Rows("40:48").EntireRow.Hidden = True
    Case 1: Rows("20:39").EntireRow.Hidden = True
End Select

End Sub

 

[offthelip]

Private Sub Worksheet_Calculate(ByVal Target As Range)

This call is invalid it should be

Private Sub Worksheet_Calculate()

End Sub

 

[carlsarlsburg]

The following code:

Private Sub Worksheet_Calculate()

Rows("1:100").EntireRow.Hidden = False
x = Range("D18").Value
Select Case x
Case 0: Rows("40:48").EntireRow.Hidden = True
Case 1: Rows("20:39").EntireRow.Hidden = True
End Select

End Sub

Results in run-time error '-2147417848 (80010108)
Method "Hidden' of object 'Range' failed

 

[Joe4]

The code works for me without error.
Do you have any protected cells or merged cells on your sheet?

 

[carlsarlsburg]

There are merged cells on my sheet. Not sure if this matters but I don't get these errors with Worksheet_SelectionChange. Cell D18 is merged with E18, unmerging these cells still results in the error.

Below are links to images of my excel file and the error.
Coversheet

651R-2 Data Sheet

Error Code