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".
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.
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
The following code is a small section of the code I have on each sheet. This is from "Sheet1" it is named "Book 1".
VBA Code:
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.
VBA 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