Macro to refresh a sheet when opening

HI All,

I have a macro here that is conditionally locking cells. I have the cells being conditionally locked based on V1, V2, and V3 as you can see below. If we say that this code is on sheet2, Then V1, V2, and V3 are coming from Sheet1, just in the form of =Sheet1$A$1.... On sheet1 i am asking the user a series of questions, and what they answer will conditionally lock this sheet. This code works great at conditionally locking the sheet. I am only having one problem and a major one at that. What happens is that for some reason the sheet must not be refreshing upon opening and therefore it will not unlock any of the cells. All the cells are locked and i have to unprotect the sheet manually then click on one of the cells and the sheet will refresh and conditionally lock based on the criteria that the user has given. I am looking for some sort of code that i can possibly put in the front of my code to refresh the page upon opening. I feel like if the page were to refresh that this would clear my problem and the conditional lock will then work perfectly upon opening the sheet. I am pretty new to VBA, so if you could post your code and where excactly to put it in my workbook/worksheet. Thank you to all for the help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim NewRange    As String, _
        TopRow      As Long
   
    With ActiveSheet
        If .Range("V1").Value <> "" And .Range("V2").Value <> "" Then
            TopRow = .Range("V3").Value / 3
            NewRange = .Range(.Cells(22 - TopRow, "B"), .Cells(22 - TopRow, "U")).Address(0, 0) & ":B22"
            .Unprotect "password"
            .Cells.Locked = True
            .Range(NewRange).Locked = False
            .Protect "password"
            .EnableSelection = xlUnlockedCells
            .Protect
            EditMode = ActiveSheet.Name
        End If
    End With
exit_routine:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

You can use the Workbook_Open event, in the ThisWorkbook module:

public sub Workbook_Open()

    Call RefreshPageLock(worksheets("Sheet1"))

end sub

In a new module:

public sub RefreshPageLock(byval wksCurr as worksheet)

    Dim NewRange    As String, _
        TopRow      As Long
   
    With wkscurr
        If .Range("V1").Value <> "" And .Range("V2").Value <> "" Then
            TopRow = .Range("V3").Value / 3
            NewRange = .Range(.Cells(22 - TopRow, "B"), .Cells(22 - TopRow, "U")).Address(0, 0) & ":B22"
            .Unprotect "password"
            .Cells.Locked = True
            .Range(NewRange).Locked = False
            .Protect "password"
            .EnableSelection = xlUnlockedCells
            .Protect
            EditMode = .Name
        End If
    End With
exit_routine:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

end sub

I would recommend changing your original code from using the worksheet_selectionchange event to using the worksheet_change event:

private sub Worksheet_Change(byval Target as Excel.Range)

    if not intersect(Target, Range("V1:V3")) is nothing then
        call refreshpagelock(me)
    endif

end sub

The selectionchange event will trigger everytime a new cell is selected, which typically happens more often than changing a cell.

I'm not sure that I have the worksheet references right, you are referring to both Sheet1 and Sheet2. That can be easily fixed if this is close to what you want.

Wallyeye,

Thank you very much for your help. Is there any way to do this using my existing code, i have to do it no a couple of worksheets that are similar. I was hoping some private sub that i could add to certain pages would do the trick. Again, thank you very much for your help

You can reference the codename and macro using:

Private Sub Workbook_Open()

Call Sheet1.Worksheet_Change(Range("V1"))
Call Sheet2.Worksheet_Change(Range("V1"))

End Sub

It is less clean doing it this way, but it will work. You do need to pass the correct range (Range("V1")) to trigger the conditional change. I would still recommend using the worksheet_change rather than the worksheet_selectionchange event.

It looks like I had mucked up my earlier code anyway, it would look more like this:

public sub Workbook_Open()

    Call RefreshPageLock(worksheets("Sheet1").Range("V1"))

end sub
public sub RefreshPageLock(byval Target as Excel.Range)

    Dim NewRange    As String, _
        TopRow      As Long

    With target.parent
        If .Range("V1").Value <> "" And .Range("V2").Value <> "" Then
            TopRow = .Range("V3").Value / 3
            NewRange = .Range(.Cells(22 - TopRow, "B"), .Cells(22 - TopRow, "U")).Address(0, 0) & ":B22"
            .Unprotect "password"
            .Cells.Locked = True
            .Range(NewRange).Locked = False
            .Protect "password"
            .EnableSelection = xlUnlockedCells
            .Protect
            EditMode = .Name
        End If
    End With
exit_routine:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

end sub

private sub Worksheet_Change(byval Target as Excel.Range)

    if not intersect(Target, Range("V1:V3")) is nothing then
        call refreshpagelock(target)
    endif

end sub

Wallyeye,

Thanks you so much for your help, I was able to tweak your code a bit to make it fit my worksheet and it worked. Thank you so much!