Do function on worksheet open

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

eli.tallman@gmail.com - 07 Dec 2007 22:54 GMT

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 to this Message

JLGWhiz - 08 Dec 2007 00:15 GMT

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.

> 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
[quoted text clipped - 17 lines]
>
> Thanks in advance.

Reply to this Message