Macro to If Statement

J

JHusker

I want to assign a macro to an if statement. After I've recorded the macro,
how do I assign it to the "=if" formula?

Thanks in advance for the help!

~Joe
 
S

Sean Timmons

May as well put the If into your VB.

Wherever the macro is,
Private Sub macroname()
'comments
Put your IF x Then
Macro code
End If
End Sub
 
C

Chip Pearson

You can't (reliably) run a macro from a worksheet cell. A cell can
call upon a function written in VBA, but that function cannot change
any part of the Excel environment, such as changing the value of
another cell. A function called from a worksheet cell (directly or
indirectly) can only return a value to the cell from which it was
called.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)
 
S

Sheeloo

Macros can not be assigned to a formula.

You can use User Defined Function to a formula...
 
J

JHusker

This is what I want to do:
if the value of the cell=0, then I want the whole row to be hidden......is
this possible?
 
J

JHusker

How do you write an IF into VB?

Sean Timmons said:
May as well put the If into your VB.

Wherever the macro is,
Private Sub macroname()
'comments
Put your IF x Then
Macro code
End If
End Sub
Click to expand...
 
C

Chip Pearson

In the Sheet's code module, use code like the following. Change the
tests of the row numbers to reflect the row(s) whose values you want
to test:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Row >= 11 And Target.Row <= 15 Then
If Target.Value = 0 Then
Target.EntireRow.Hidden = True
Else
Target.EntireRow.Hidden = False
End If
End If
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)
 
S

Sean Timmons

It is possible. I suppose you'd want the code. :-(

You would probably get best results in the Excel Programming section...

I know you'd want to use soemthign such as

If Range("A1).Value = 0 Then
Selection.entireRow.Hidden
End If

But not sure of the function this would require...
 
Top