Method 'Range' of object '_Global' failed - I'm stuck!

I have a "greeting" tab where I instruct the user to enable macros to gain access to the workbook.

With macros enabled, I display all sheets and hide the greeting. I'm using a range name "HideActSheet" to store the Active Sheet (index #) and select the previously active sheet after I display all the sheets and hide the greeting. It doesn't matter if I use an index number (numeric) vs. text. I have used range names like this to store values and typically will hide them from the user - historically without problem in Excel 2007. I'm relatively new to Excel 2010 and not sure how to resolve this problem.

The following code is in the This WorkBook VBA Module:

Option Explicit

Private Sub Workbook_Open() 'ONE PROBLEM LINE OF CODE IN THIS PROCEDURE - SEE BELOW.
Dim i01 As Integer, ws As Worksheet
Range("A1").Select
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
ActiveWindow.DisplayWorkbookTabs = True
Sheet1.Visible = xlSheetVeryHidden 'This line hides the Greeting tab
i01 = Range("HideActSheet").Value '<<<<----This line generates the Method 'Range' of object '_Global' failed error
Sheets(i01).Select
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'NO PROBLEMS WITH THIS PROCEDURE.
Dim i01 As Integer, ws As Worksheet
i01 = ActiveWorkbook.ActiveSheet.Index
Names.Add Name:="HideActSheet", RefersToR1C1:="=" & i01, Visible:=True
Sheet1.Visible = xlSheetVisible
Sheet1.Select
Range("A6").Select
Sheets(i01).Select
ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If ws.CodeName <> "Sheet1" Then
ws.Visible = xlSheetVeryHidden
End If
Next ws
End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean) 'ONE PROBLEM LINE OF CODE IN THIS PROCEDURE - SEE BELOW
Dim i01 As Integer, ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.CodeName <> "Sheet1" Then
ws.Visible = xlSheetVisible
Else
ws.Activate
Range("A6").Select
End If
Next ws
ActiveWorkbook.Sheets(1).Visible = xlSheetVeryHidden
ActiveWindow.DisplayWorkbookTabs = True
Application.ScreenUpdating = True
i01 = Range("HideActSheet").Value '<<<<----This line generates the Method 'Range' of object '_Global' failed error
Sheets(i01).Select
End Sub

Vishesh's picture

Another reason could be the

Another reason could be the scope of the range HideActSheet
If its a sheet specific range and not a global range then you can call as

Thisworkbook.Worksheets("SheetName").Range("HideActSheet").Value

Change the SheetName as per your workbook.

No, the name is global or has

No, the name is global or has a Workbook level scope. I did come up with a work around where I name a cell "SWITCH001" and entered "=HideSheetValue" which pulls in the value (no problem). Next I substituted "SWITCH001" for the HideSheetValue in the previous code. I don't like the work around and want to understand why I can't do this anymore in 2010 or what I'm doing wrong? SWITCH000 also has a Workbook level scope. Most confused.

Thank you

Vishesh's picture

ReplaceRange("HideActSheet1"

Replace

Range("HideActSheet1").Value

with

Split(ThisWorkbook.Names("HideActSheet").Value, "=")(1)

whereever you are reading/writing HideActSheet

WOW - you solution worked,

WOW - you solution worked, but I'm not sure why! I've done some work with arrays and this variable is a single numeric value? I will study your solution some more and I'd appreciate any insight.

Thank you!

if you go: insert => name =>

if you go: insert => name => define..
do you see the range: HideActSheet ?

alternatively, press CTRL + G and paste HideActSheet... then press enter

if it doesn't take you to the range, then it looks like it has been deleted

Yes, you can see the name

Yes, you can see the name defined and the value is correct. I just can't access the name from VBA? The name is not a sheet specific or sheet level name; the scope is "Workbook". I'm working in Excel 2010 and its Formulas on the ribbon then Name Manager. I'm thinking its a quirk with 2010 - I ventured into this a few weeks agow.

Thanks

is the workbook active when

is the workbook active when you're accessing the range ?

Yes, single workbook.

Yes, single workbook.

attach an example sheet....

attach an example sheet.... login and edit your post

Vishesh solution below worked

Vishesh solution below worked - if you still want to take a look at the issue, I will be glad to post.