Excel VBA macro Diagram 1 as chart 1

[Peter90]

Hy everybody!
I have got a problem because of the Excel language pack perhaps... I've got a Diagram 1 (hungarian name of the chart) and the Excel macro refers to Chart 1 in the english version of the Excel. In the Hungarian version the macro in the macro record function enter the Diagram 1 name. How could I make equal the two name in the macro?

Diagram as Chart 1 (back and forth)

So this is the problem:




Dim z As String

z = "Chart 1" = "Diagram 1"

ActiveSheet.Shapes(z).ScaleWidth 2.3, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes(z).ScaleHeight 2.9, msoFalse, _
msoScaleFromTopLeft

But it's failing all over the time and I don't know why.

Please help me,
Thank you in advance!

 

[Jerry Sullivan]

Hi Peter,

It is failing because the code will compare the values "Chart 1" and "Diagram 1" and always assign "False" to the variable z

You can confirm that with this simple test...

Sub Test()
 Dim z As String
   
 z = "Chart 1" = "Diagram 1"
 MsgBox "Z= " & z
   
End Sub

If you need to be able to vary your code based on the Language Pack that is being used by the Application, you could try this....

Sub ShapeNameBasedOnLanguageID()
 Dim sShapeName As String

 Select Case Application.LanguageSettings.LanguageID(msoLanguageIDUI)
   '--Hungarian
   Case msoLanguageIDHungarian: sShapeName = "Diagram 1"
   '--default is English
   Case Else: sShapeName = "Chart 1"
 End Select
 
 With ActiveSheet.Shapes(sShapeName)
   .ScaleWidth 2.3, msoFalse, msoScaleFromTopLeft
   .ScaleHeight 2.9, msoFalse, msoScaleFromTopLeft
 End With
      
End Sub

 

[Andrew Poulsom]

Just another possibility if the worksheet contains only one Chart:

z = ActiveSheet.ChartObjects(1).Name

 

[Peter90]

Wow!
Thank you very much! I used the last solution, because I have only one chart on the sheet, but I note all of the solutions, these may come in handy.