Pop-up box for element-selection via VBA

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

  • Pop-up box for element-selection via VBA

    Hello, I started working with palo some days ago. Now I'm searching for a possibility to open the pop-up box for element selection via VBA (the box, that appears if I make a double click on an element field). Something like

    Range("A2").Select
    Application.DoubleClick

    didn't work. Could anybody help me?

    Thanx a lot
  • RE: Pop-up box for element-selection via VBA

    Hi,
    first set a VBA reference on the "Jedox.Palo.XlAddin.tlb". You find it in e.g. "c:\program files\Jedox\Palo\". Here is a function you can use for showing the element selector. It returns the selected element name.

    Public Function getElementBySelection(serverdb As String, Dimension As String)

    'e.g. try a call with "localhost/Demo" as serverdb and "Products" as dimension

    Dim obj As Jedox_Palo_XlAddin.IPaloEngineCom
    Set obj = New Jedox_Palo_XlAddin.ComInterface

    getElementBySelection = obj.ChooseElements(serverdb, Dimension)


    End Function

    Holger

    The post was edited 1 time, last by h_decker ().

  • RE: Pop-up box for element-selection via VBA

    Hi Holger,
    many thanks for your help. It seems to work fine. Now I have the following question about the VBA reference to the tlb-file:

    Normally, users don't have this reference in Excel/VBA. How can I implement this that users get this reference automatically when they start the excel file (or the function). And how can I get dynamically the program path of Jedox Palo, where I find this tlb-file (e.g. from a registry-key. Not all users have the same Palo-programpath. So I can't use a fixed string).

    Thank you for your help :)
  • RE: Pop-up box for element-selection via VBA

    Usually Excel tries to restore the reference if it can find it. But of course you can set it by your own using application with e.g.

    ThisWorkbook.VBProject.References.AddFromFile ...
    or
    ThisWorkbook.VBProject.References.AddFromGuid ...

    You find information about palo path e.g. in HKEY_USERS\.DEFAULT\Software\Jedox\Palo\XlAddin

    Holger
  • RE: Pop-up box for element-selection via VBA

    Thank you for this information. I think, now I can work forward.

    One little detail question: it's possible, to select a default element in the pop-up box for element-selection? I like to select the element in the box, which was given to the report by the last selection.

    Thank you very much
  • RE: Pop-up box for element-selection via VBA

    ...or is there a possibility to set a doubleclick on a cell per vba (Application.DoubleClick doesn't work)? When I do a doubleclick manually, the 'old' element in the pop-up box is marked. I'd like to mark the actual element that's selected in the report per vba (so that if the user presses the 'OK'-Button without a selection before, the correct element will be choosen and the user will have a better orientation when the active element is marked).

    Or if you have a workaround, please let me know.

    Thank you.
  • RE: Pop-up box for element-selection via VBA

    Goodday,

    My question might sound a little bit strange to you but could you please explain to me how to set the VBA reference as written below:

    "first set a VBA reference on the "Jedox.Palo.XlAddin.tlb". You find it in e.g. "c:\program files\Jedox\Palo\". Here is a function you can use for showing the element selector. It returns the selected element name.
    (written by h_decker)".

    An step by step example would be great!

    Sorry I'm a newbie! :rolleyes:

    Thanks a lot!
    Liv
  • RE: Pop-up box for element-selection via VBA

    Hi,
    open Excel VBA Editor. e g. via ALT+F11. Then choose Tools in Dropdownmenu. There should be a menue ntry entry called References. In german Excel VBAS its Menu "Extras" and "Verweise". Then browse for the explained file.
    Hope that helps.

    Holger