Automatically update SheetName in workbook sub if SheetName changes
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 |
Automatically update SheetName in workbook sub if SheetNamechanges
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 |
Automatically update SheetName in workbook sub if SheetName changes
Another way is to reference the sheet by it's codename, which will not
change when the tab name does. In the tree view of worksheets to the left of the code module you see Sheet1(Warning), Sheet2(Area), Sheet3(MySheet)... you can refer to Sheet2 like this: ThisWorkbook.Sheet2.Visible = xlSheetVeryHidden This will address Sheet2 no matter what tab name is assigned to it by the cell value. Mike F wrote in message ... 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 |
Automatically update SheetName in workbook sub if SheetNamechanges
Thanks to everyone. A couple of tweaks here and there to the code
using the suggestions did the trick. |
All times are GMT +1. The time now is 08:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com