Hi
If G3 always has the new sheet name then you could try
1. Put this at the top of your code module
Public NewSheetName as String

2. In the sub that captures the new sheet name do

NewSheetName = Range("G3").Text

3. Now you can use this in other subs like as
ThisWorkbook.Sheets(NewSheetName).Visible = xlSheetVeryHidden

regards
Paul

On Feb 29, 11:23*am, wrote:
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