VBA Code to hide cells based upon cell on another sheet

M

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:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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:
Upvote 0
miamiman33176 said:
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?
Click to expand...

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")
 
Upvote 0
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)
trumpexcel.com

Excel VBA Events - An Easy (and Complete) Guide

Excel VBA Events allow you to run a macro when a specific event occurs. An event could be an action such as opening a new workbook, inserting a new worksheet, double-clicking on a cell, etc. In this tutorial, I cover everything there is to know about Excel VBA Events - with useful examples.
trumpexcel.com trumpexcel.com

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Solved
VBA help.
2
Replies
10
Views
803
Peter_SSs
Peter_SSs
A
  • Solved
how to protect sheets vba code
2
Replies
19
Views
1K
Scuba94
S
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
1K
kevin9999
K
A
  • Question Question
Using a cell on another worksheet to run my code?
Replies
1
Views
400
rlv01
R
J
  • Question Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
1K
dmt32
D

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top