Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
external usenet poster
 
Posts: 789
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
external usenet poster
 
Posts: 1,092
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Does anyone see this .xls]sheetname? Eric Excel Worksheet Functions 2 January 21st 07 04:28 PM
Does anyone see this .xls]sheetname? Eric Excel Discussion (Misc queries) 2 January 21st 07 04:04 PM
Sheetname formula John S Excel Worksheet Functions 3 December 5th 06 08:34 PM
i need the sheetname in my cells Kiklop Excel Worksheet Functions 5 September 15th 06 06:35 PM
Returning Sheetname Andrew Excel Discussion (Misc queries) 5 May 26th 05 11:54 AM


All times are GMT +1. The time now is 09:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"