R
RichardRosema
Hi
I'm using a fairly basic code to get a sheet name to equal a cell in a
worksheet.
Private Sub unpro()
Sub Worksheet_SelectionChange(ByVal Target As Range)
Unprotect "provision"
ActiveSheet.Name = Range("g3").Value
Protect Password:="provision"
End Sub
However when range G3 changes, the following code (in ThisWorkbook)
fails - particularly at:
ThisWorkbook.Sheets("Area").Visible = xlSheetVeryHidden
ThisWorkbook code is:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
HideSheets
End Sub
Private Sub Workbook_Open()
UnhideSheets
End Sub
Private Sub HideSheets()
Dim sht As Object
Application.ScreenUpdating = False
ThisWorkbook.Sheets("Warning").Visible = xlSheetVisible
'This sheet contains a message to the user.
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Warning" Then sht.Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
Next sht
ThisWorkbook.Save
End Sub
Private Sub UnhideSheets()
Dim sht As Object
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
Next sht
ThisWorkbook.Sheets("Area").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub
So of course, what I'd ideally need is for the workbook code to
recognise when the worksheet private sub changes the name of the tab.
E.g. SheetName "Area" might change to SheetName "Zone".
Any help would be appreciated. Please note I'm a VB novice and
knowledge is less than rudimentary.
Thanks
I'm using a fairly basic code to get a sheet name to equal a cell in a
worksheet.
Private Sub unpro()
Sub Worksheet_SelectionChange(ByVal Target As Range)
Unprotect "provision"
ActiveSheet.Name = Range("g3").Value
Protect Password:="provision"
End Sub
However when range G3 changes, the following code (in ThisWorkbook)
fails - particularly at:
ThisWorkbook.Sheets("Area").Visible = xlSheetVeryHidden
ThisWorkbook code is:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
HideSheets
End Sub
Private Sub Workbook_Open()
UnhideSheets
End Sub
Private Sub HideSheets()
Dim sht As Object
Application.ScreenUpdating = False
ThisWorkbook.Sheets("Warning").Visible = xlSheetVisible
'This sheet contains a message to the user.
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Warning" Then sht.Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
Next sht
ThisWorkbook.Save
End Sub
Private Sub UnhideSheets()
Dim sht As Object
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
Next sht
ThisWorkbook.Sheets("Area").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub
So of course, what I'd ideally need is for the workbook code to
recognise when the worksheet private sub changes the name of the tab.
E.g. SheetName "Area" might change to SheetName "Zone".
Any help would be appreciated. Please note I'm a VB novice and
knowledge is less than rudimentary.
Thanks