Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do function on worksheet open
I am trying to set a function to run when my worksheet is open. I
have found the workbook_open() function and if I have to I suppose I can use this to do what I am trying to do. I want a process to run when the worksheet is first opened that will search a specific column for a specific value, and if found, hide the rows the value is found on. I currently use this code to hid the rows when the value is entered initially: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("M:M")) Is Nothing Then If Target.Value = "Y" Then Target.EntireRow.Hidden = True End If End Sub I want this new functionality so that if the hidden data is unhidden, I do not have to re-hide it manually. I would like Excel to do this for me when the file is first opened. What is the best way to do this? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do function on worksheet open
With the worksheet_change procedure, each row will be hidden as a "Y" is
entered in column "M". With Workbook_Open or Worksheet_Activate you would need to evaluate the entire column for all "Y" entries and hide each one as found. Private Sub [Workbook_Open] or [Worksheet_Activate] () 'Pick one Dim c As Range With Worksheets(?) 'enter the sheet name or index number For Each c In .Range("M2:M" & .Cells(Rows.Count, 13).End(xlUp)).Row If c.Value = "Y" Then c.EntireRow.Hidden = True End If Next End With End Sub This is untested code. Try it on a copy before using in original. " wrote: I am trying to set a function to run when my worksheet is open. I have found the workbook_open() function and if I have to I suppose I can use this to do what I am trying to do. I want a process to run when the worksheet is first opened that will search a specific column for a specific value, and if found, hide the rows the value is found on. I currently use this code to hid the rows when the value is entered initially: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("M:M")) Is Nothing Then If Target.Value = "Y" Then Target.EntireRow.Hidden = True End If End Sub I want this new functionality so that if the hidden data is unhidden, I do not have to re-hide it manually. I would like Excel to do this for me when the file is first opened. What is the best way to do this? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007, Automatically open new worksheet on open. | Excel Discussion (Misc queries) | |||
Unable to open worksheet when called through a function | Excel Programming | |||
select open webpage, copy information to open worksheet | Excel Programming | |||
workbooks.open function fails to open an existing excel file when used in ASP, but works in VB. | Excel Programming |