Inside an excel workbook I have sheet1 and sheet2.
In Sheet1 I have a cell that changes dynamically (cell A1).
In Sheet2 I have a cell (B1) that copies the value from cell A1 from Sheet1.
On Sheet 2 cells will hide when the value in B1 is 0.
Here is the VBA code from sheet2
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B1").Value = "0" Then
Columns("E:I").EntireColumn.Hidden = True
Else
Columns("E:I").EntireColumn.Hidden = False
End If
End Sub
The thing is that when I change the value on Sheet1 nothing happens on Sheet2, and I don´t know why.
Answer
Use the below code in sheet 1 calculate event,
Private Sub Worksheet_Calculate()
If Range("B1").Value = "0" Then
Sheet2.Columns("E:I").EntireColumn.Hidden = True
Else
Sheet2.Columns("E:I").EntireColumn.Hidden = False
End If
End Sub
and do some calculation in sheet1-B1 cell(say =0+0), if the result is 0 then it will hide the columns E:I in Sheet2.
No comments:
Post a Comment