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
<(E-Mail Removed)> wrote in message
news:cc085425-0ab9-4080-8467-(E-Mail Removed)...
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, RichardRos...@googlemail.com 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
|
Similar Threads
