VBA Code to hide cells based upon cell on another sheet

miamiman33176

Board Regular
Joined
Jan 9, 2009
Messages
97
I am trying to get this formula to work based upon a value on another cell on another sheet. The VBA code works very well when I select and place a number in the "BX3" cell, but when I use a reference to "ADMIN!J13" it will not work. Even if I tell cell "BX3" to value "ADMIN!J13" it will not work. If I tell "BX3" to get its value from "ADMIN!J13" it works once, when I click out of "BX3" and ir will not work again. Below is my code. Any suggestions?

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("BX3")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    Select Case Target.Address(0, 0)
    Case "BX3"
      Select Case Target.Value
Case Is = "0": Rows("8:60").EntireRow.Hidden = True

        Case Is = "1": Rows("9:60").EntireRow.Hidden = True
        Rows("7:8").EntireRow.Hidden = False
     Case Is = "2": Rows("10:60").EntireRow.Hidden = True
        Rows("8:9").EntireRow.Hidden = False
     Case Is = "3": Rows("11:60").EntireRow.Hidden = True
        Rows("8:10").EntireRow.Hidden = False
     Case Is = "4": Rows("12:60").EntireRow.Hidden = True
        Rows("8:11").EntireRow.Hidden = False
     Case Is = "5": Rows("13:60").EntireRow.Hidden = True
        Rows("8:12").EntireRow.Hidden = False
     Case Is = "6": Rows("14:60").EntireRow.Hidden = True
        Rows("8:13").EntireRow.Hidden = False
     Case Is = "7": Rows("15:60").EntireRow.Hidden = True
        Rows("8:14").EntireRow.Hidden = False
     Case Is = "8": Rows("16:60").EntireRow.Hidden = True
        Rows("8:15").EntireRow.Hidden = False
     Case Is = "9": Rows("17:60").EntireRow.Hidden = True
        Rows("8:16").EntireRow.Hidden = False
     Case Is = "10": Rows("18:60").EntireRow.Hidden = True
        Rows("8:17").EntireRow.Hidden = False
     Case Is = "11": Rows("19:60").EntireRow.Hidden = True
        Rows("8:18").EntireRow.Hidden = False
     Case Is = "12": Rows("20:60").EntireRow.Hidden = True
        Rows("8:19").EntireRow.Hidden = False
     Case Is = "13": Rows("21:60").EntireRow.Hidden = True
        Rows("8:20").EntireRow.Hidden = False
     Case Is = "14": Rows("22:60").EntireRow.Hidden = True
        Rows("8:21").EntireRow.Hidden = False
     Case Is = "15": Rows("23:60").EntireRow.Hidden = True
        Rows("8:22").EntireRow.Hidden = False
     Case Is = "16": Rows("24:60").EntireRow.Hidden = True
        Rows("8:23").EntireRow.Hidden = False
     Case Is = "17": Rows("25:60").EntireRow.Hidden = True
        Rows("8:24").EntireRow.Hidden = False
     Case Is = "18": Rows("26:60").EntireRow.Hidden = True
        Rows("8:25").EntireRow.Hidden = False
     Case Is = "19": Rows("27:60").EntireRow.Hidden = True
        Rows("8:26").EntireRow.Hidden = False
     Case Is = "20": Rows("28:60").EntireRow.Hidden = True
        Rows("8:27").EntireRow.Hidden = False
     Case Is = "21": Rows("29:60").EntireRow.Hidden = True
        Rows("8:28").EntireRow.Hidden = False
     Case Is = "22": Rows("30:60").EntireRow.Hidden = True
        Rows("8:29").EntireRow.Hidden = False
     Case Is = "23": Rows("31:60").EntireRow.Hidden = True
        Rows("8:30").EntireRow.Hidden = False
     Case Is = "24": Rows("32:60").EntireRow.Hidden = True
        Rows("8:31").EntireRow.Hidden = False
     Case Is = "25": Rows("33:60").EntireRow.Hidden = True
        Rows("8:32").EntireRow.Hidden = False
     Case Is = "26": Rows("34:60").EntireRow.Hidden = True
        Rows("8:33").EntireRow.Hidden = False
     Case Is = "27": Rows("35:60").EntireRow.Hidden = True
        Rows("8:34").EntireRow.Hidden = False
     Case Is = "28": Rows("36:60").EntireRow.Hidden = True
        Rows("8:35").EntireRow.Hidden = False
     Case Is = "29": Rows("37:60").EntireRow.Hidden = True
        Rows("8:36").EntireRow.Hidden = False
     Case Is = "30": Rows("38:60").EntireRow.Hidden = True
        Rows("8:37").EntireRow.Hidden = False
     Case Is = "31": Rows("39:60").EntireRow.Hidden = True
        Rows("8:38").EntireRow.Hidden = False
     Case Is = "32": Rows("40:60").EntireRow.Hidden = True
        Rows("8:39").EntireRow.Hidden = False
     Case Is = "33": Rows("41:60").EntireRow.Hidden = True
        Rows("8:40").EntireRow.Hidden = False
     Case Is = "34": Rows("42:60").EntireRow.Hidden = True
        Rows("8:41").EntireRow.Hidden = False
     Case Is = "35": Rows("43:60").EntireRow.Hidden = True
        Rows("8:42").EntireRow.Hidden = False
     Case Is = "36": Rows("44:60").EntireRow.Hidden = True
        Rows("8:43").EntireRow.Hidden = False
     Case Is = "37": Rows("45:60").EntireRow.Hidden = True
        Rows("8:44").EntireRow.Hidden = False
     Case Is = "38": Rows("46:60").EntireRow.Hidden = True
        Rows("8:45").EntireRow.Hidden = False
     Case Is = "39": Rows("47:60").EntireRow.Hidden = True
        Rows("8:46").EntireRow.Hidden = False
     Case Is = "40": Rows("48:60").EntireRow.Hidden = True
        Rows("8:47").EntireRow.Hidden = False
     Case Is = "41": Rows("49:60").EntireRow.Hidden = True
        Rows("8:48").EntireRow.Hidden = False
     Case Is = "42": Rows("50:60").EntireRow.Hidden = True
        Rows("8:49").EntireRow.Hidden = False
     Case Is = "43": Rows("51:60").EntireRow.Hidden = True
        Rows("8:50").EntireRow.Hidden = False
     Case Is = "44": Rows("52:60").EntireRow.Hidden = True
        Rows("8:51").EntireRow.Hidden = False
     Case Is = "45": Rows("53:60").EntireRow.Hidden = True
        Rows("8:52").EntireRow.Hidden = False
     Case Is = "46": Rows("54:60").EntireRow.Hidden = True
        Rows("8:53").EntireRow.Hidden = False
     Case Is = "47": Rows("55:60").EntireRow.Hidden = True
        Rows("8:54").EntireRow.Hidden = False
     Case Is = "48": Rows("56:60").EntireRow.Hidden = True
        Rows("8:55").EntireRow.Hidden = False
     Case Is = "49": Rows("57:60").EntireRow.Hidden = True
        Rows("8:56").EntireRow.Hidden = False
     Case Is = "50": Rows("58:60").EntireRow.Hidden = True
        Rows("8:57").EntireRow.Hidden = False
     Case Is = "51": Rows("59:60").EntireRow.Hidden = True
        Rows("8:58").EntireRow.Hidden = False
     Case Is = "52": Rows("59:60").EntireRow.Hidden = True
        Rows("8:59").EntireRow.Hidden = False
        Case Is > 52: Rows("8:59").EntireRow.Hidden = True 'Canada range is hidde
Rows("60:61").EntireRow.Hidden = False
    End Select
    End Select
  End If
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Berore I answer your question (I will do that in a separate post) ....

1 For future posts - with long code please click on VBA and paste code inside code tags to make code easier to read etc

2. You could reduce repitition in your code by using a 4 variables to capture the first and last row numbers of visible/hidden ranges
(I think I have correctly mirrored the logic of your code - check it carefully)
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim h1 As Long, h2 As Long, v1 As Long, v2 As Long
        
    If Not Intersect(Target, Range("BX3")) Is Nothing Then
        If Target.CountLarge > 1 Then Exit Sub
        If Not IsNumeric(Target) Then Exit Sub      'prevent code failing
        h1 = 8 + Target
        h2 = 60
        v1 = 8
        v2 = 7 + Target
          
        Select Case Target.Address(0, 0)
            Case "BX3"
                Select Case Target.Value
                    Case 0
                        v1 = 1: v2 = 1            'any row that is always visible
                    Case 1
                        v1 = 7: v2 = 8
                    Case 52
                        h1 = 59: h2 = 60
                    Case Is > 52
                        h1 = 8: h2 = 59: v1 = 60: v2 = 61
                End Select
            End Select
          
            Rows(h1 & ":" & h2).EntireRow.Hidden = True
            Rows(v1 & ":" & v2).EntireRow.Hidden = False
              
        End If
End Sub

Are you planning to use other cells to do anything?
- this appears redundant as your code currently stands - is there a reason why it is in your code?
Select Case Target.Address(0, 0) ... Case "BX3
- there are no other cases
If Not Intersect(Target, Range("BX3")) Is Nothing Then has already told the code what to use as a trigger
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
I am trying to get this formula to work based upon a value on another cell on another sheet. The VBA code works very well when I select and place a number in the "BX3" cell, but when I use a reference to "ADMIN!J13" it will not work. Even if I tell cell "BX3" to value "ADMIN!J13" it will not work. If I tell "BX3" to get its value from "ADMIN!J13" it works once, when I click out of "BX3" and ir will not work again. Below is my code. Any suggestions?

Code below triggers when the sheet is activated and uses the value in J13 in sheet admin to determine which rows are visible/hidden
Place code in the same code window as Worksheet_Change
Delete Worksheet_Change if the value in BX3 is determined by formula (you have already discovered that it does not work)

VBA Code:
Private Sub Worksheet_Activate()
    Dim h1 As Long, h2 As Long, v1 As Long, v2 As Long, cel As Range
    Set cel = Sheets("Admin").Range("J13")
    h1 = 8 + cel
    h2 = 60
    v1 = 8
    v2 = 7 + cel
        Select Case cel
            Case 0
                v1 = 1: v2 = 1            'any row that is always visible
            Case 1
                v1 = 7: v2 = 8
            Case 52
                h1 = 59: h2 = 60
            Case Is > 52
                h1 = 8: h2 = 59: v1 = 60: v2 = 61
        End Select
    Rows(h1 & ":" & h2).EntireRow.Hidden = True
    Rows(v1 & ":" & v2).EntireRow.Hidden = False
End Sub

If you prefer to use the value in BX3
Rich (BB code):
Replace:
    Set cel = Sheets("Admin").Range("J13")
With:
    Set cel = Range("BX3")
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Here is a useful guide to event macros (it does not tell you all the answers but you can test for yourself using my method below)

If I am not sure that an event macro will trigger when something happens... I put a message box as the first line of an empty event macro and ... test to see whether what I want to do triggers that event.
I test before writing any detailed code.
I only write the code AFTER I get that simple trigger to work
 

Watch MrExcel Video

Forum statistics

Threads
1,127,457
Messages
5,624,855
Members
416,063
Latest member
chaulon199

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top