Define name via Macro in Sheet scope

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • Define name via Macro in Sheet scope

    Hello everyone,

    in my Report I have a Combobox, which defines a name.
    Now I would like to set the value of this name in the Macro function __open () to a default value.
    Unfortunately the name of the ComboBox has the sheet scope and I only manage to adress a name on workbook scope via the Macro.
    ActiveWorkbook()->names->item('xxxxxx')->refers_to = "=".'"'.ActiveWorkbook()->names->item('yyyyyyy')->value.'"';
    This just creates a new name with the higher scope.

    This does not work:
    ActiveWorkbook()->sheets('Sheet1')->names->item('xxxxxxx')->refers_to = "=".'"'.ActiveWorkbook()->names->item('yyyyyyy')->value.'"';

    I could solve the isue by defining a variable, which is connected to the ComboBox, but I want to reduce the effect to a local name.
    With the variable I need to define a unique variable for all reports.

    Thank you for your help!
  • Hi,

    currently it is not possible to access worksheet-scoped named ranges in macros.

    What you could do as a workaround for your scenario: point the Combobox to some cell as target; then create a sheet-scoped named range with that cell as source. Your __open() macro would then define the value of this cell, thus changing both the value selected in Combobox when opening, and the value of the named range.