Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hiding columns; dynamic
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hiding columns; dynamic
On Dec 9, 4:38*pm, cate wrote:
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
hiding columns; dynamic
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. "cate" wrote in message ... On Dec 9, 4:38 pm, cate wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
hiding columns; dynamic
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. "JLGWhiz" wrote in message ... 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. "cate" wrote in message ... On Dec 9, 4:38 pm, cate wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding Columns | Excel Discussion (Misc queries) | |||
Hiding zeros in dynamic charts | Charts and Charting in Excel | |||
hiding columns | Excel Programming | |||
Help with hiding columns | Excel Programming | |||
Hiding Columns | Excel Programming |