Why can I not see a Named Range that is scoped as Workbook from any VBA code?

0
votes

I have created a Named Range using the Name Manager. It is called Vol_Check. It is scoped as Workbook.

If it is scoped as workbook, why can I not see it in VBA code from other sheets or ThisWorkbook or Modules. Even when I try to reference it directly, it will not work. Here is a code example that I cannot make work.

Private Sub CommandButton1_Click()

    If ThisWorkbook.Sheets("sheet1").Range("Vol_Check").Value <> 1 Then
        MsgBox ("Some message ")
    End If

End Sub
3
excelvba
Why go stick the worksheet in there if it's scoped to the workbook? Just ThisWorkbook.Range("Vol_Check").value should work. Are you getting any errors? Does that range contain multiple cells, or just one? Do you get anything in the "immediate" window when you run debug.print Range("Vol_Check").value? - JNevill
ThisWorkbook.Range("Vol_Check").value generates a compile error. Method or data member not found. - rtemen
My range has one cell. when I run my original code above, I get a subscript out of range error. - rtemen
@rtemen see my answer below - Shai Rado

3 Answers

1
votes

If you are using a Named Range "Vol_Check", then use the code below to read a value from one of the cells inside the Named Range.

In the example, let's say your Named Range includes Range B2:B2, then the code line Range("Vol_Check")(1, 1) refers to Cell B2 

Private Sub CommandButton1_Click()

' just for debug - shows the first row and first column in the Named Range
MsgBox Range("Vol_Check")(1, 1)

If Range("Vol_Check")(1, 1) <> 1 Then
    MsgBox ("Some message ")
End If

End Sub
0
votes

Try using Worksheet.Evaluate instead:

Private Sub CommandButton1_Click()
    If ThisWorkbook.Sheets(1).Evaluate("Vol_Check").Value <> 1 Then
        MsgBox "Some message "
    End If
End Sub
0
votes

Problem solved. It turns out that I was not defining the Named Range properly with the Name Manager tool. Even tho I was marking it 'Workbook' in scope, my other error was messing it up. Thanks everyone. Rich