Running Macro on Excel Sheet Opening

0

Hi. I have written a code to unrortect a sheet, find a specific cell (which i want to bring to the viewable screen) and then lock the sheet again on opening of a workbook. Unfortunately I can't get it to bring the cell into the viewable screen or lock the sheet. Can anyone help??

Private Sub Workbook_Open()

Sheets("view only").Activate
ActiveSheet.Unprotect Password:="holidays"
For Each cell In ActiveSheet.Range("A1:H214")
    If cell.Value = [Today()] Then
    cell.Select
    ActiveCell.Offset(1).Select
ActiveSheet.Protect Password:="holidays", Structure:=True, Windows:=True
    End If
Next
End Sub
Answer
Discuss

Answers

0

Your code can't work because it loops through all the cells A1:H214. You want it to stop looping when it finds TODAY(). The command to use is Exit For. In the same breath, you don't need to select the cell where the date was found if you wish to select the cell below it. Here is a corrected version of your code. Except for the protection bits it is tested and works.

Private Sub Workbook_Open()
    Worksheets("view only").Activate
    ActiveSheet.Unprotect Password:="holidays"
    For Each Cell In ActiveSheet.Range("A1:H214")
        If Cell.Value = [Today()] Then
            Cell.Offset(1).Select
            Exit For
        End If
    Next Cell
    ActiveSheet.Protect Password:="holidays", Structure:=True, Windows:=True
End Sub

The thing that hurts my eye when looking at your code is that the variable isn't declared. That is the source of millions of baffled beginner hours and tons of their pulled hair. It is wrongly claimed that adding Option Explicit at the top of your code will "force" you to declare variables. The better truth is that it will help you find undeclared variables. If you always declare your variables undeclared variables won't give you a headache. The code below corrects that omission.

I also found it useful to know when I last edited a procedure. Therefore I add the date. Finally, code becomes easier to read if you start by saying what you intend to do next. For this purpose the With statement is useful. All your code is about Worksheets("view only"). So you introduce it at the start, add End With at the end and precede every reference to it with a period which takes the place of its full name. Below is your exact code, beautified to my preference. I hope you like it too.

Private Sub Workbook_Open()
    ' 25 Sep 2018
    Dim Cell As Range
    
    With Worksheets("view only")
        .Activate
        .Unprotect Password:="holidays"
        For Each Cell In .Range("A1:H214")
            If Cell.Value = [Today()] Then
                Cell.Offset(1).Select
                Exit For
            End If
        Next Cell
        .Protect Password:="holidays", Structure:=True, Windows:=True
    End With
End Sub

Observe the indenting which is very helpful for reading code. Reading code is important when trouble-shooting. The more readable your code is the less time you will spend on searching for coding errors. With better indenting you might have found the missing loop exit yourself. Most definitely you would have seens that the sheet protection must be applied after the looping is done and irrespective of whether TODAY() was found or not. BTW, workbooks have Sheets and Worksheets. Every Worksheet is a Sheet but not every Sheet is a Worksheet.

Your code potentially loops through 1712 cells to find TODAY(). Excel can do that a couple of hundred times faster than VBA. Use VBA's Find method to harness Excel to do this job. The code below does that.

Private Sub Workbook_Open()
    ' 25 Sep 2018
    Dim Rng As Range
    
    With Worksheets("view only")
        Set Rng = .Range("A1:H214")
        .Activate
        .Unprotect Password:="holidays"
        On Error Resume Next
        Rng.Find("=TODAY()").Offset(1).Select
        If Err Then Rng.Find(CDate(Format(Date, "Short Date"))).Offset(1).Select
        .Protect Password:="holidays", Structure:=True, Windows:=True
    End With
End Sub

In this procedure the range to be searched is declared because it is used twice. After declaring it, Rng.Find is exactly the same as  .Range("A1:H214").Find. Since the range includes a hard-coded address you don't want it in the code more than once, in case it ever needs to be changed.

 Rng.Find("=TODAY()") finds the cell containing the date and Rng.Find("=TODAY()").Offset(1).Select selects the cell below it, all in one line of code. But what if it isn't found? The preceding line On Error Resume Next caters to this eventuality. If there is an error, meaning TODAY() wasn't found, the code makes a second attempt. It now looks for "25/09/2018". Using Find with dates is a little complicated. Since this post is already long enough, suffice it to say that I included this line to enable you to look for a hard-entered date if the need should arise. Date is VBA's equivalent for Excel's TODAY(). You can replace "Date" with any other date in this formula, Rng.Find(CDate(Format(Date, "Short Date")))

If the Find attempts fail nothing will be selected. That is the result of all three variations of your code in this post, including the corrected version of your original procedure.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login