Unhide based on Calculate event

[Bronz]

Hey all,
I need help with a macro to unhide a row based on a forumla result, and repeat this process any number of times.

Example, A1 is set with a forumla to change based on data entered into A3. I need Row 2 to unhide based on A1's result. Then I need to repeat this process for entering data into B3, where Row 3 would then become unhidden.

I have used the change event, and it works fine, but I would rather restrict the unhiding to actual data entering, and not cursor moves.
Current code:
[VBA]
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
[/VBA]

Thanks in advance. I'm

[JONvdHeyden]

Hello and welcome to VBAX

You could and possibly should use the calculate event. But if you want to avoid it being triggered when merely moving about then change Worksheet_SelectionChange to Worksheet_Change.

Also bear in mind that you can restrict the macro to run only when the target falls within a specified range.

E.g:
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub 'column A only
'rest of code
End Sub
[/vba]

I can possible help more if you explain what results drive hide / unhide.

Hope this helps.

[Bob Phillips]

There is a lot of superfluousness in that code.

You test a condition againts True, which returns True or False, which you test for True again

[vba]

If (Rows(Target.Row + 1).EntireRow.Hidden = True) = True Then
[/vba]

can be reduced to

[vba]

If Rows(Target.Row + 1).EntireRow.Hidden = True Then
[/vba]

or even

[vba]

If Rows(Target.Row + 1).EntireRow.Hidden Then
[/vba]

Also, When you test Rows, that is the entirerow, so it can even reduce to

[vba]

If Rows(Target.Row + 1).Hidden Then
[/vba]

[JONvdHeyden]

Further, this may be a suitable substitute for your entire If statemet:
[vba]ActiveSheet.Unprotect
Rows(Target.Row + 1).Hidden = Not Rows(Target.Row + 1).Hidden
ActiveSheet.Protect[/vba]

...works like a toggle

[Bronz]

Thanks for the quick replies. I am very new to VBA so can only go off of examples, and everything I saw made me want to check the condition (true) against a logic statement.

But again, thanks.

I am essentially trying to open up the next row after the current row has had something inputted. Kind of like a carriage return on a typewriter. I am hiding the other rows because I want to limit the visibility, and not every user will use every row (far from it), but I still need standardization.

I've since changed the event to the change event (my newness again), and it's working, but I still want to figure this out for on calculate.

Thanks again.


Edit: I thought that .hidden was a method, and you had to specifically state if it was true or not. Does .hidden simply toggle the existing state?

[Bob Phillips]

Edit: I thought that .hidden was a method, and you had to specifically state if it was true or not. Does .hidden simply toggle the existing state?

No it doesn't,. Hidden is a Property, and it is either True or False, so there is no need to test against True, that is like saying

If True = True Then

it works, but hardly necessary.

[Bronz]

Thanks for the clarification. That helps.

As an aside, do you happen to know of any good resources for basic tutorials on VBA? There's a lot of different methodologies out there, and everything I've found does things differently, and only list examples. I just need a clarification on the syntax.

[nst1107]

For many questions you might have, Help files are all you need. Most of the time, you can simply click on the property, object, etc. in question and hit F1. Can't get much easier or cheaper than that. Books will take you a step further, but make sure you actually need them before you buy them. They aren't cheap.