VBA Case NamedRange.value

[Kamolga]

Hi,

I build a template in multiple languages. I therefore set up a named range "Language" that can be "Fr", "Eng" or "Nl" (those values are not stored in any cell but through a userform when opening the template). All my titles are looking up on this value so everything in the forms changes accordingly.

Now that I want to change sheet names and chart titles with VBA, nothing is happening with my case selection

If I type

Debug.Print ActiveWorkbook.Names("Language").Value

I get

="Nl"

in my immediate window

If I use

[LEFT][COLOR=#222222][FONT=Verdana]ShChart.name="Tabel"[/FONT][/COLOR][/LEFT]

my sheet changes name perfectly
but if I type

 Select Case ActiveWorkbook.Names("Language").Value
             Case "Nl"
              ShChart.name="Tabel"
End select

nothing happens. I have tried Case is ="Nl" without success

 

[bobsan42]

I would recommend you to be more specific when referencing objects.
How have you set the ShChart name ? Which is actually the ActiveWorkbook?

Try to debug. Put these lines just before Select Case:

Debug.print "language", ActiveWorkbook.Names("Language").Value
Debug.print "Workbook", ActiveWorkbook.Name,  ActiveWorkbook.path
Debug.print "Chart", ShChart.Name,"parentWB",ShChart.Parent.Name

and see if there is anything not in order.

 

[pgc01]

Hi

First, this is not a named range, this name does not refer to a range. This is a named constant.

2 options

1 -
You posted that the name value is

="Nl"

in this case you have to use:

Select Case ActiveWorkbook.Names("Language").Value
    Case "=""Nl"""
        Sheet8.Name = "Tabel"
End select

2 -
You evaluate the name to get the constant it refers to.

In this case the code is simpler:

Select Case Evaluate(ActiveWorkbook.Names("Language").Value)
    Case "Nl"
        Sheet8.Name = "Tabel"
End Select

Please try

 

[Kamolga]

Hi,

Thank you for the answer. Evaluate is working perfectly and would have never thaught about it.

No success with 1st option. I even tried to change

.Value with .RefersToR1C1 and "=""Nl""" (which is what I get if I change the constant value while recording) without success.​

 

[pgc01]

I'm glad it works OK.

No success with 1st option.

Both should work.

What do you get when you run

Sub test()

' if name "Language" exists delete it
On Error Resume Next
ActiveWorkbook.Names("Language").Delete
On Error GoTo 0

' add the name "Language" referring to the constant "Nl"
ActiveWorkbook.Names.Add Name:="Language", RefersTo:="=""Nl"""

Select Case ActiveWorkbook.Names("Language").Value
    Case "=""Nl""": MsgBox "OK1"
End Select

Select Case Evaluate(ActiveWorkbook.Names("Language").Value)
    Case "Nl": MsgBox "OK2"
End Select

End Sub

 

[Kamolga]

I just realised the success depended if I was already on the sheet or not , not the value "=""Nl""" or evaluate()

​

. Indeed I receive Ok1 and OK2 and both work now <strike></strike>

 

[pgc01]

I'm glad you figured it out. Cheers!