hiding columns; dynamic

[cate]

This won't work... functions cannot "access" anything outside of its
own cell? So, I need a sheet event to run this.

After a cell value changes anywhere on the sheet, or something, I want
to recognize 'calculations complete' and then run a column hiding sub
(). Or, I think that's what I want. I need to hide columns based on
data and it appears a user function won't do it. Suggestions? Thank
you in advance.


NO GO below (doesn't hide itself) (maybe a bug?)

Function columnVisibility(hide_if_lt_zero As Long)

Dim myRng As Range
Set myRng = Application.ThisCell

If hide_if_lt_zero < 0 Then
myRng.EntireColumn.Hidden = True
Else
myRng.EntireColumn.Hidden = False
End If

columnVisibility = 0#

End Function

 

[cate]

This won't work... functions cannot "access" anything outside of its
own cell?  So, I need a sheet event to run this.

After a cell value changes anywhere on the sheet, or something, I want
to recognize 'calculations complete' and then run a column hiding sub
().  Or, I think that's what I want.  I need to hide columns based on
data and it appears a user function won't do it.  Suggestions?  Thank
you in advance.

NO GO below (doesn't hide itself)  (maybe a bug?)

Function columnVisibility(hide_if_lt_zero As Long)

    Dim myRng As Range
    Set myRng = Application.ThisCell

    If hide_if_lt_zero < 0 Then
        myRng.EntireColumn.Hidden = True
    Else
        myRng.EntireColumn.Hidden = False
    End If

    columnVisibility = 0#

End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Range) seems to
work. I can't figure out exactly when this runs. Is this event
first? If it is, can I call something to continue the processing and
wait for all other calculations to end? Should I worry?

 

[JLGWhiz]

You can probably use either the Worksheet_SelectionChange or the
Worksheet_Change event to do what you want. It is how you write the code
that matters. Lets say that Range("B5") is the one that you want to control
the actions. Then using the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("B5") Then Exit Sub
If Range("B5") <= 0 Then
ActiveCell.Column.Hidden = True
End If
End Sub

This is untested, but should set you in the right direction.

This won't work... functions cannot "access" anything outside of its
own cell? So, I need a sheet event to run this.

After a cell value changes anywhere on the sheet, or something, I want
to recognize 'calculations complete' and then run a column hiding sub
(). Or, I think that's what I want. I need to hide columns based on
data and it appears a user function won't do it. Suggestions? Thank
you in advance.

NO GO below (doesn't hide itself) (maybe a bug?)

Function columnVisibility(hide_if_lt_zero As Long)

Dim myRng As Range
Set myRng = Application.ThisCell

If hide_if_lt_zero < 0 Then
myRng.EntireColumn.Hidden = True
Else
myRng.EntireColumn.Hidden = False
End If

columnVisibility = 0#

End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Range) seems to
work. I can't figure out exactly when this runs. Is this event
first? If it is, can I call something to continue the processing and
wait for all other calculations to end? Should I worry?

 

[JLGWhiz]

This modified code will work to hide the column that B5 is in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("B5") Then Exit Sub
If Range("B5") <= 0 Then
Columns(Target.Column).Hidden = True
End If
End Sub


To use the code, copy it to the worksheet code module. Alt + F11 and double
click on the sheet name that you want the code to work on. That will open
the sheet code module. Save the workbook then type something in B5, then
type zero in B5 and see if the column hides.