On calculate (or change) hide

Bronz

New Member
Joined
Feb 11, 2008
Messages
13
I don't often post here, but I have a question. I need an excel macro to run on a change of a cell and unhide the row below that cell based on the results of a formula in that hidden row (happens to be offset).

Essentially I need this.
- Change is made to cell
- Based on results of a cell in the row below, unhide that row

I am unsure how to do this, as I am fairly new to the VBA world. I know I need to attach it to the oncalculate or change event, but I am unsure how to get the current cell that is changing and go from there. Any help would be appreciated.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
In the Change event, "Target" refers to the cell that was just changed.

Target.Row, Target.Column, Target.Value, etc.

From there, "Target.Row + 1" will refer to the hidden row below it.

Rows(Target.Row + 1).EntireRow.Hidden = False

Post more details if you need help with the actual code.
 
Upvote 0
That certainly helps, as I didn't know that. But I think I am better off on the Calculate event.

I am a VBA newb, so please bear with me.

What I want to do is like this.

Setup: Row 2 is hidden. In row A1, an indicator is set to see if A2 is no longer blank.
What I need to do: Unhide row 2 once A2 is no longer blank (A1 indicator changes to match). This needs to be repeated for other rows with similar setups.

Again, thanks for the help. And I apologize for my newness.
 
Last edited:
Upvote 0
Perhaps there is a way to get the cell you were just in from the calculate event instead of the change event?
 
Upvote 0
Wow, I don't pay attention. Change those A2's to B1's.

Row 1 is visible, Row 2 is hidden. User enters data into Row 1, which unhides Row 2.

I am using an indicator in case I want multiple cases for anything. So once A2 is written in, A1 changes. I need Row 2 to become unhidden based upon A1's result, and this to be repeatable down the line. So once B2 is visible, and filled in, Row 3 becomes unhidden etc.

Thanks again.
 
Upvote 0
Perhaps there is a way to get the cell you were just in from the calculate event instead of the change event?

If this is possible, what I want to do would be pretty easy.

I can do this now with the change event, but it realistically will not work with users of the sheet just moving around with the arrows. Right now I have this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (Rows(Target.Row + 1).EntireRow.Hidden = True) = True Then
ActiveSheet.Unprotect
Rows(Target.Row + 1).EntireRow.Hidden = False
Target.Select
ActiveSheet.Protect

End If
End Sub


Which of course is a very simple piece of code. If I can just reference the previous active cell on a calculate event I could do much the same thing.
 
Upvote 0
If the cell in column A of a row (in rows 1-20) is changed to "show next", this routine will reveal the next row. It will hide those rows whose col A value changes to anything else.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Row < 20 Then
        With Target.EntireRow
            .Offset(1, 0).Hidden = Not (.Range("A1").Value = "show next")
        End With
    End If
End Sub
 
Upvote 0
Thanks, that helps a lot.

Quick question on the basics. What does the keyword With do? It looks like it just sets an object up to be used, and uses that object until you end the with? Am I right on that?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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