1

I am using a VBA code to hide some rows based on a cell value:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("K3"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
        Case Is = "Full_FC_powered":        Rows("33:33").EntireRow.Hidden = True
                                            Rows("37:38").EntireRow.Hidden = True
                                            Rows("45:46").EntireRow.Hidden = True
        Case Is = "FC_for_hotel":           Rows("33:33").EntireRow.Hidden = False
                                            Rows("37:38").EntireRow.Hidden = False
                                            Rows("45:46").EntireRow.Hidden = False
        Case Is = "DG_for_transit":         Rows("33:33").EntireRow.Hidden = False
                                            Rows("37:38").EntireRow.Hidden = False
                                            Rows("45:46").EntireRow.Hidden = False
        End Select
End If
End Sub

The code itself already works but there is one downfall: The value of 'K3' is linked to another worksheet where it gets calculated. However, if the value of K3 changes in the other worksheet, the VBA code does not automattically work. I first have to go to cell K3 and press enter.

My question: is there a way to link this code directly to another worksheet or to refresh this code when the cell values change?

Thanks in advance

braX
  • 11,506
  • 5
  • 20
  • 33
Berendvv
  • 75
  • 8
  • Change `Worksheet_Change` for `Worksheet_Calculate` and make your calculation automatic on your workbook. – Damian May 08 '20 at 09:37
  • Thanks for the quick reply, how do I 'make my calculation automatic on my workbook'? If i just change Worksheet_Change(ByVal Target As Range) to Worksheet_Calculate() i get a 'runtime 424 error: object required' – Berendvv May 08 '20 at 10:11
  • You can set on the inmediate window (ctrl+G in VBA editor) like this: `Application.Calculation = xlAutomatic`. As for the code, ignore everything about the target, so ignore the conditional and change `Select Case Target.Value` for `Select Case Range("K3").Value` this will trigger the procedure everytime your worksheet calculates. I'd suggest to disable the screenupdate at the beginning and turn it back on at the end. – Damian May 08 '20 at 10:15
  • You can see from the VBE that the `Calculate` event does not have any arguments. – SJR May 08 '20 at 10:26

1 Answers1

0

Single Cell Worksheet_Calculate

Cell I3 is used instead of a cell on an other sheet i.e. the formula in K3 is: =I3.

enter image description here

enter image description here

enter image description here

enter image description here

All three codes have to be used for this to work properly. Adjust Sheet1 in Public Const TargetSheet As String = "Sheet1" appropriately.

Standard Module Code e.g. Module1

Option Explicit

Public TargetValue As String
Public Const TargetCell As String = "K3"
Public Const TargetSheet As String = "Sheet1"

Sub HideShowRows(Sheet As Worksheet)
    Dim rng As Range
    With Sheet
        Set rng = Union(.Rows("33"), .Rows("37:38"), .Rows("45:46"))
        Select Case .Range(TargetCell).Value
            Case "Full_FC_powered": rng.EntireRow.Hidden = True
            Case "FC_for_hotel", "DG_for_transit": rng.EntireRow.Hidden = False
        End Select
        TargetValue = .Range(TargetCell).Value
    End With
End Sub

ThisWorkbook Code

Option Explicit

Private Sub Workbook_Open()
    TargetValue = Worksheets(TargetSheet).Range(TargetCell).Value
    HideShowRows Worksheets(TargetSheet)
End Sub

Sheet Code e.g. Sheet1

Option Explicit

Private Sub Worksheet_Calculate()
    If Range(TargetCell).Value <> TargetValue Then
        HideShowRows Me
    End If
End Sub

' The following in this case are the same:

'    If Range(TargetCell).Value <> TargetValue Then
'        HideShowRows Me
'    End If

'    If Worksheets(TargetSheet).Range(TargetCell).Value <> TargetValue Then
'        HideShowRows Worksheet(TargetSheet)
'    End If
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Thanks for the very elaborate answer, however it does not work. I changed the string to the name of the worksheet. When I run it excel just closes itself. – Berendvv May 08 '20 at 11:00
  • You don't have to run anything. It is automatic. Check once more if you have put the codes in the right places. They are in three different places: `Module` code, `ThisWorkbook` code and `Sheet` code. `Sheet` (`"Sheet1"`) refers to the name in the tabs i.e. the names in parentheses `()` in VBE. – VBasic2008 May 08 '20 at 11:07
  • @Berendvv: Here is the link to [my file](https://drive.google.com/open?id=1XkfdKNWEgNkydmaGcW5jNXlAwX-0koCI) on Google Drive. When trying, copy different values from the `G` column to cell `I3` and see the rows hide or show. If any other value is entered, there will be no change (omitted `Case Else`). – VBasic2008 May 08 '20 at 11:34