How can I invoke running a macro from within an "IF" function.

[Guest]

I want to Hide a range of rows (16:18 in this example) if "no" is in a range
of cells and Unhide the same range of rows if "yes" is in the same range of
cells. The "Hide", "Unhide" macros (below) work if I select one from the Run
Macro list, of course. But, how can I call for it to run from an "IF"
function. I know this statement is wrong, but it may help you know what I am
looking for: =IF (A1:A15="no",Run Hide,IF (A1:A15="yes",Run Unhide),"")

Sub Hide()
'
' Hide Macro
'
Rows("16:18").Select
Selection.EntireRow.Hidden = True
End Sub
___________________
Sub Unhide()
'
' Unhide Macro
'
Rows("16:18").Select
Selection.EntireRow.Hidden = False
End Sub

 

[Dave Peterson]

You can't run macros from formulas, but it might be easier than you think.

Select your range.
data|filter|autofilter

Then use the dropdown arrow in the first cell in column A and choose to show the
Yes's (or use custom, not equal to "no").

It's a very easy way to hide/show rows.

Data|filter|show all

will show all the data.

 

[Guest]

Thanks. However, the intent is this. The default values in A1:A15 will be
"no" If a person answers yes to one of those cells, I want specific other
rows to unfold (unhide) revealing some additional data to be inserted by the
person.

 

[Dave Peterson]

How do you know which rows to hide/unhide?

If you specify

A1="no" --> hide rows x:y
A2="no" --> hide rows z:w
....

You may find that you get an acceptable response.

 

[Guest]

In this case I am using a worksheet as a type of form. If a person answers
"yes" in A15, Rows 16, 17 & 18 would unfold for value entries by the person.
If the default "no" remains unchanged Rows 16 thru 18 will remain hidden and
the person will simply move to the next question on Row 19.

 

[Otto Moehrbach]

As Dave said, you can't run a macro from an IF function in a cell. However,
you can run a macro with a Worksheet_Change event macro if the event macro
is coded to run your macro when the cell referenced by your IF function
changes to the value ("no") you say.
Something like:
Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, SomeRange) Is Nothing Then _
If UCase(Target.Value) = "NO" Then Call TheMacroName
End Sub
Post back with more details if you want to go this route and need more.
HTH Otto

 

[Dave Peterson]

I'm not sure what rows control what ever rows, so I started in row 15 and went 4
rows at a time.

rightclick on the worksheet tab that should have this behavior. Select view
code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'A15 controls Rows 16, 17 & 18
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a15:a99")) Is Nothing Then Exit Sub

'15,19,23,27, ... are the controlling rows.
If (Target.Row + 1) Mod 4 = 0 Then
Application.EnableEvents = False
Target.Offset(1, 0).Resize(3, 1).EntireRow.Hidden _
= CBool(LCase(Target.Value) = "no")
Application.EnableEvents = True
End If

End Sub

No hides the rows. Anything else shows them.

In this case I am using a worksheet as a type of form. If a person answers
"yes" in A15, Rows 16, 17 & 18 would unfold for value entries by the person.
If the default "no" remains unchanged Rows 16 thru 18 will remain hidden and
the person will simply move to the next question on Row 19.

 

[Guest]

Thanks. This works great. One last question (I think). How would that be
rewritten if the controling cell is a random cell (without logical sequence)
and the Hide/Unhide rows after the controling cell vary from one row to more.

Example:
If "no" is entered into A4, Hide Rows 5 thru 10.
If "no" is entered into A22, Hide Rows 23 thru 24.
If "no" is entered into A53, Hide Rows 54 thru 77.
Etc.

 

[Guest]

If the answer is a simple Worksheet Change that gets repeated with variable
chnages, that's OK. Just give me the statements for the three examples and I
will know how to continue the repetition. Clearly, I am very ignorant of VBA.

Thanks. This works great. One last question (I think). How would that be
rewritten if the controling cell is a random cell (without logical sequence)
and the Hide/Unhide rows after the controling cell vary from one row to more.

Example:
If "no" is entered into A4, Hide Rows 5 thru 10.
If "no" is entered into A22, Hide Rows 23 thru 24.
If "no" is entered into A53, Hide Rows 54 thru 77.
Etc.

 

[Dave Peterson]

The bad news is that etc. stuff. I don't see a pattern. And if there isn't a
pattern, the only way I know how to do it is to explicitly tell excel what I
want to do:

This is one way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'If "no" is entered into A4, Hide Rows 5 thru 10.
'If "no" is entered into A22, Hide Rows 23 thru 24.
'If "no" is entered into A53, Hide Rows 54 thru 77.

Dim myAddrToCheck As Variant
Dim myRowsToHideShow As Variant
Dim iCtr As Long
Dim InTheRange As Boolean
Dim testRng As Range

myAddrToCheck = Array("a4", "a22", "a53")
myRowsToHideShow = Array("5:10", "23:24", "54:77")

If UBound(myAddrToCheck) <> UBound(myRowsToHideShow) Then
MsgBox "Design error #1"
Exit Sub
End If

If Target.Cells.Count > 1 Then Exit Sub

InTheRange = False
For iCtr = LBound(myAddrToCheck) To UBound(myAddrToCheck)
If LCase(Target.Address(0, 0)) = LCase(myAddrToCheck(iCtr)) Then
InTheRange = True
Exit For
End If
Next iCtr

If InTheRange = False Then Exit Sub

Set testRng = Nothing
On Error Resume Next
Set testRng = Me.Range(myRowsToHideShow(iCtr))
On Error GoTo 0

If testRng Is Nothing Then
MsgBox "Design error #2"
Exit Sub
End If

Application.EnableEvents = False
testRng.EntireRow.Hidden = CBool(LCase(Target.Value) = "no")
Application.EnableEvents = True

End Sub

Thanks. This works great. One last question (I think). How would that be
rewritten if the controling cell is a random cell (without logical sequence)
and the Hide/Unhide rows after the controling cell vary from one row to more.

Example:
If "no" is entered into A4, Hide Rows 5 thru 10.
If "no" is entered into A22, Hide Rows 23 thru 24.
If "no" is entered into A53, Hide Rows 54 thru 77.
Etc.

 

[Guest]

Wow. You're great. I simply modified your two lines as below and now I can
control the event.
myAddrToCheck = Array("a4", "a22", "a53", "a100")
myRowsToHideShow = Array("5:10", "23:24", "54:77", "101:150")

Thanks, again.

 

[Guest]

Thanks. But, my ignorance of VBA would not allow me to make this work. This
is what I did to test. I opened a new worksheet. I recorded a macro called
"TheMacroName" (I am aware that I could have modified the Name as long as
they match, but I chose not to). I pasted your Worksheet Change to tab Sheet
1. I could not find a cell where "NO" would run the macro (I didn't know
which line in your statement to modify set the target for my "NO".

Allow me to re-phrase the goal. The controling cell ("NO") is a random cell
(without logical sequence) and the Hide/Unhide rows after the controling cell
vary from one row to more.

Example:
If "no" is entered into A4, Hide Rows 5 thru 10.
If "no" is entered into A22, Hide Rows 23 thru 24.
If "no" is entered into A53, Hide Rows 54 thru 77.
Etc.

If the answer is a simple Worksheet Change that gets repeated with variable
chnages, that's OK. Just give me the statements for the three examples and I
will know how to continue the repetition. Clearly, I am very ignorant of VBA.