Changing Thisworkbook code with code on individual sheets

[XrayLemi]

Good morning all. I cannot find an answer anywhere for help with this.

The following code is a small section of the code I have on each sheet. This is from "Sheet1" it is named "Book 1".

Dim e As Range, y As Range
    Set e = Range("N5000")
    Set e = Intersect(Target, e)
    If Not e Is Nothing Then
   Application.EnableEvents = False
     For Each y In e
      Select Case True
       Case 14 = y.Column 'N
        If y.Value <> "" Then
         Check = MsgBox("Are your entries correct?" & vbCrLf & "After entering yes, These values CANNOT be changed.", vbYesNo + vbQuestion, "Cell Lock Notification")
           If Check = vbYes Then
             Target.Rows.EntireRow.Locked = True
             If Cells(y.Row, "R").Value <> "" Then Copyemail 'R
             If Cells(y.Row, "S").Value <> "" Then ThisWorkbook.Save 'S
             Sheets("Book 2").Range("B6:G6").Locked = False
             Sheets("Book 2").Range("I6:K6").Locked = False
             Sheets("Book 2").Range("M6").Locked = False
             Sheets("Book 2").Select
' New code needs to go here
             Else
             Cells(y.Row, "N").Value = ""
           End If
        End If
      Case Else
    End Select
   Next y
 End If

The last row a user can enter any data on any sheet is 5000. After that the current sheet locks and the next sheet activates. The code above locks "Sheet1" and activates "Sheet2" after a "Yes" entry in the message box. The problem I have is in the Thisworkbook module/section. Part of the code insures the file opens to a specific sheet. I now need it to change as the sheet changes. Here is that code.

Private Sub Workbook_Open()

ThisWorkbook.Unprotect "Password"

 Worksheets("Sheet1").Visible = xlSheetVeryHidden
 Worksheets("Sheet2").Visible = xlSheetVeryHidden
 
 ThisWorkbook.Protect "Password", Structure:=True, Windows:=False

  Dim WS As Worksheet
  For Each WS In Worksheets
  WS.Activate
    WS.Protect "Password", UserInterfaceOnly:=True, DrawingObjects:=True, _
      Contents:=True, Scenarios:=True, AllowFiltering:=True
        Rows("1").EntireRow.Hidden = True
        Columns("P:U").EntireColumn.Hidden = True
  Next WS

  'This is the line I need to change each time the code on the current sheet activates the next sheet
  Sheets("Book 1").Select
  
UpdateDataFromMasterFile

End Sub

I was HOPING there was a simple way to add a few lines to the sheet code that will update the Thisworkbook code to the next activated sheet.
Is this even possible?
Thank you in advance,
Jim

 

[Snakehips]

@XrayLemi Maybe something like below?
Assumes that each sheet's code will have the name of the next sheet to open as and when the sheet itself is considered full?
Needs you to have a cell, somewhere, that you can use to hold the name of the current opening sheet. Eg Sheet1 cell X1

Dim e As Range, y As Range
    Set e = Range("N5000")
    Set e = Intersect(Target, e)
    If Not e Is Nothing Then
   Application.EnableEvents = False
     For Each y In e
      Select Case True
       Case 14 = y.Column 'N
        If y.Value <> "" Then
         Check = MsgBox("Are your entries correct?" & vbCrLf & "After entering yes, These values CANNOT be changed.", vbYesNo + vbQuestion, "Cell Lock Notification")
           If Check = vbYes Then
             Target.Rows.EntireRow.Locked = True
             If Cells(y.Row, "R").Value <> "" Then Copyemail 'R
             If Cells(y.Row, "S").Value <> "" Then ThisWorkbook.Save 'S
             Sheets("Book 2").Range("B6:G6").Locked = False
             Sheets("Book 2").Range("I6:K6").Locked = False
             Sheets("Book 2").Range("M6").Locked = False
             Sheets("Book 2").Select
' New code needs to go here
'*************
Sheets("Sheet1").Range("X1") = "Book 2"   '<< or wherever it is convenient to store the revised opening sheet name
'*************
             Else
             Cells(y.Row, "N").Value = ""
           End If
        End If
      Case Else
    End Select
   Next y
 End If

Private Sub Workbook_Open()

ThisWorkbook.Unprotect "Password"

 Worksheets("Sheet1").Visible = xlSheetVeryHidden
 Worksheets("Sheet2").Visible = xlSheetVeryHidden
 
 ThisWorkbook.Protect "Password", Structure:=True, Windows:=False

  Dim WS As Worksheet
  For Each WS In Worksheets
  WS.Activate
    WS.Protect "Password", UserInterfaceOnly:=True, DrawingObjects:=True, _
      Contents:=True, Scenarios:=True, AllowFiltering:=True
        Rows("1").EntireRow.Hidden = True
        Columns("P:U").EntireColumn.Hidden = True
  Next WS

  'This is the line I need to change each time the code on the current sheet activates the next sheet
'*************
Dim ShName As String
ShName = Sheets("Sheet1").Range("X1")   '<< or wherever
Sheets(ShName).Select
'*************
UpdateDataFromMasterFile

End Sub

Hope that helps.

 

[dmt32]

Hi,

First, as you have stated that you have only shared part of your code my workings are just general ideas you may be able to adapt in to your project

Next, if your worksheet event code largely does the same thing for each worksheet, you do not need a Change Event in each sheet you would use the Workbook_SheetChange event in the ThisWorkbook code page.

This event has two parameters, Sh & Target. You use the Target parameter in normal way & the Sh parameter to apply to only those sheets in the workbook code applies to.

I have only glanced at your code but as far as I could determine, you are just checking when cell N5000 has data entered? and if so, lock that row & move to next sheet.

If this is correct then maybe this update to your code will go in right direction for what you want.

Make a backup of your workbook & the DELETE ALL the sheet change events.

Place this code in the Thisworkbook Code Page

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim EndRange         As Range
    Dim SheetIndex       As Long
    Dim Check            As VbMsgBoxResult
   
    If Not Sh.Name Like "Book*" Then Exit Sub
   
    SheetIndex = Val(Mid(Sh.Name, 6)) + 1
   
    On Error GoTo myerror
   
    Set EndRange = Intersect(Target, Sh.Range("N5000"))
   
    If Not EndRange Is Nothing Then 'N
   
        Application.EnableEvents = False
       
        If EndRange.Value <> "" Then
            Check = MsgBox("Are your entries correct?" & vbCrLf & _
                    "After entering yes, These values CANNOT be changed.", _
                    vbYesNo + vbQuestion, "Cell Lock Notification")
           
            If Check = vbYes Then
                Target.Rows.EntireRow.Locked = True
                If Sh.Cells(EndRange.Row, "R").Value <> "" Then CopyeMail        'R
                If Sh.Cells(EndRange.Row, "S").Value <> "" Then ThisWorkbook.Save 'S
               
                'next sheet
                With Worksheets("Book " & SheetIndex)
                    .Activate
                    .Range("B6:G6,I6:K6,M6").Locked = False
                End With
               
                ' New code needs to go here
            Else
                Sh.Cells(EndRange.Row, "N").Value = ""
            End If
        End If
    End If
   
myerror:
    Application.EnableEvents = True
End Sub

To repeat, this is just a general idea, you will need to determine if suggested changes will meet your project need.

With regard to the Open event, assuming that any sheet in the workbook named Book* has cell N5000 UNLOCKED you can check for this in your For Next loop & then select that sheet

Private Sub Workbook_Open()
    Dim ws              As Worksheet
    Dim blnSelected     As Boolean
   
    Const strPassword As String = "Password"
   
     With ThisWorkbook
    
        .Unprotect strPassword
        .Worksheets("Sheet1").Visible = xlSheetVeryHidden
        .Worksheets("Sheet2").Visible = xlSheetVeryHidden
   
    For Each ws In .Worksheets
       
        ws.Protect strPassword, UserInterfaceOnly:=True, DrawingObjects:=True, _
                   Contents:=True, Scenarios:=True, AllowFiltering:=True
                         
        ws.Rows("1").EntireRow.Hidden = True
        ws.Columns("P:U").EntireColumn.Hidden = True
       
        If ws.Name Like "Book*" And Not blnSelected Then _
        If Not ws.Range("N5000").Locked Then ws.Select: blnSelected = True
       
    Next ws
   
        .Protect strPassword, Structure:=True, Windows:=False
       
    End With
   
    UpdateDataFromMasterFile
   
End Sub

Dave





Dave

 

[XrayLemi]

Hello Gentlemen,
I took parts of what both of you had suggested, added, modified, and got a great solution!

Snakehips (Tony?), I used your idea of a cell to hold a search value. Rather than the sheet name, I used this formula in cell R1 =IF(D5001<>"","X",""). When Cell D5001 gets an entry, either by me or by my code, the cell value in R1 changes.

Dave, I changed one line in the Open event code in Thisworkbook that you gave me to look for the change in R1

If Not ws.Range("R1").Value = "X" Then ws.Select: blnSelected = True

This seems to work perfectly.

Thank you Both for the help!