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.

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.