Hide/Unhide rows with CASE based on cell value with formula

J

jvandeliefvoort

New Member
Joined
Nov 10, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I am quite new to VBA, and I cannot find an answer to my problem. I am using code to hide/unhide rows. It works when I put a value in cel B3, but this value must be a value on another sheet. So B3:
Excel Formula: 
='Main Sheet 2.0'!H21
. So it only works if I put for example just the value 7 in B3, but not when I use the formula.

This is the code I used:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = ("$B$3") Then
        Select Case Target.Value
        Case "1"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:13").EntireRow.Hidden = False
        Case "2"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:22").EntireRow.Hidden = False
        Case "3"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:31").EntireRow.Hidden = False
        Case "4"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:40").EntireRow.Hidden = False
        Case "5"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:49").EntireRow.Hidden = False
        Case "6"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:58").EntireRow.Hidden = False
        Case "7"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:67").EntireRow.Hidden = False
        Case "8"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:76").EntireRow.Hidden = False
        Case "9"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:85").EntireRow.Hidden = False
        Case "10"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:94").EntireRow.Hidden = False
End Select
End If
End Sub

Thank you in advance!
 
Sort by date Sort by votes
Actually there is another way via Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Place the code into the 'ThisWorkbook Module
That will allow you to use the formula approach.
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Oops! upon futher investigation that requires manual change also. Sorry.
 
Upvote 0
Here is the way for my previous suggestion of Private Sub Worksheet_Calculate(). This assumes Sheet1 as the sheet with the formula, change that to the proper sheet name.

In a regular module place the following code:
VBA Code: 
Public B3FormulaValue As Variant

In the ThisWorkbook module place the following code: Remember, change the sheet name to the sheet with the formula in it. ;)
VBA Code: 
Private Sub Workbook_Open()
    B3FormulaValue = Sheets("Sheet1").Range("B3").Value
End Sub

And then right click the sheet that has the formula in it, select 'view code' and then place the following code:
VBA Code: 
Private Sub Worksheet_Calculate()
    If Range("B3").Value <> B3FormulaValue Then
        Select Case Range("B3").Value
            Case "1"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:13").EntireRow.Hidden = False
            Case "2"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:22").EntireRow.Hidden = False
            Case "3"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:31").EntireRow.Hidden = False
            Case "4"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:40").EntireRow.Hidden = False
            Case "5"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:49").EntireRow.Hidden = False
            Case "6"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:58").EntireRow.Hidden = False
            Case "7"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:67").EntireRow.Hidden = False
            Case "8"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:76").EntireRow.Hidden = False
            Case "9"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:85").EntireRow.Hidden = False
            Case "10"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:94").EntireRow.Hidden = False
        End Select
'
        B3FormulaValue = Range("B3").Value
    End If
End Sub

Save/Close/Reopen workbook and you should be good.
 
Upvote 0
Far better (IMO) to run the code when a cell is actually changed, rather than using a calculate event & global variable.
 
Upvote 0
Fluff said:
Far better (IMO) to run the code when a cell is actually changed, rather than using a calculate event & global variable.
Click to expand...
I was just showing the OP how it could be done with the formula approach as was requested. A manual cell change detection is easier to set up, no disagreement there.
 
Upvote 0
Even easier method to use formula. Just right click the sheet with the formula, select view code, then paste the following:

VBA Code: 
Private Sub Worksheet_Calculate()
'
    Dim target As Range
'
    Set target = Range("B3:B3")
'
    If Not Intersect(target, Range("B3:B3")) Is Nothing Then
        Select Case target.Value
            Case "1"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:13").EntireRow.Hidden = False
            Case "2"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:22").EntireRow.Hidden = False
            Case "3"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:31").EntireRow.Hidden = False
            Case "4"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:40").EntireRow.Hidden = False
            Case "5"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:49").EntireRow.Hidden = False
            Case "6"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:58").EntireRow.Hidden = False
            Case "7"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:67").EntireRow.Hidden = False
            Case "8"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:76").EntireRow.Hidden = False
            Case "9"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:85").EntireRow.Hidden = False
            Case "10"
                Rows("7:94").EntireRow.Hidden = True
                Rows("7:94").EntireRow.Hidden = False
        End Select
    End If
End Sub

No other steps needed. Basically the equivalent of the Worksheet_Change event that recognizes the result of the formula.
 
Upvote 0
These two lines are redundant as the 2nd line will always be true
VBA Code: 
    Set target = Range("B3:B3")
'
    If Not Intersect(target, Range("B3:B3")) Is Nothing Then
You might as well just use
VBA Code: 
Select Case Range("B3").Value
although the code will run whenever any cell on the sheet is recalculated, therefore far better to use what I suggested which will only run when needed.
 
Upvote 0
Fluff said:
... although the code will run whenever any cell on the sheet is recalculated, therefore far better to use what I suggested which will only run when needed.
Click to expand...
Dang it!

Well I can't see any other way to do it without a Global variable being used.

Post #14 does work properly. It could, however, be done in two steps instead of three.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Using a cell on another worksheet to run my code?
Replies
1
Views
282
rlv01
R
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
413
Peter_SSs
Peter_SSs
R
  • Solved
Hide rows according to checkboxes on seperate sheet
Replies
8
Views
178
John_w
J
M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
522
kevin9999
K
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
562
kevin9999
K

Forum statistics

Threads
1,215,853
Messages
6,127,334
Members
449,376
Latest member
karenmccabe

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