Forum Discussion

bottaxxgp's avatar
bottaxxgp
Copper Contributor
Jan 10, 2024

Excel for Web: Control equivalent to the VBA messagebox

Hello, there is a control in Excel for web to obtain a pop up message during a script execution?   Thank you
Excel for web
mtarler's avatar
mtarler
Silver Contributor
Jan 10, 2024
I wonder ... could you had the script insert and show that new sheet with the message and also add an 'OK' button that is linked to another script that will then hide that sheet? I've never tried to have a script add a button to another script and don't recall a way to do that off hand, but that could be slick. If not, can you pre-create a sheet in the workbook with the colored range and pseudo message box styling and the OK button to hide that sheet so then the script only needs to unhide that sheet and insert the corresponding message in the correct range.
  • bottaxxgp's avatar
    bottaxxgp
    Copper Contributor
    Jan 10, 2024
    mtarler good solution.
    I cannot create button programmatically linked to a script but i can create some sheet only for show the message and pre-create the button with the Ok button to close the "message sheet"

    The only problem is that if the user forget to press Ok, i leave a "message sheet" visible.....
    In any case, it's a next step towards a type of pop up that I wasn't able to imagine before.

    I could also hide all the other sheets present and leave only the current message sheet, forcing the user to do the only thing possible in that sheet. Press the OK button
    • mtarler's avatar
      mtarler
      Silver Contributor
      Jan 10, 2024
      yes and in addition to hiding the other sheets I would also lock the workbook to prevent them from doing anything else or try deleting the sheet or what not...
      • bottaxxgp's avatar
        bottaxxgp
        Copper Contributor
        Jan 18, 2024

        I created a sheet with some shape to simulate a Msgbox.
        I added manually and Excel assign some default names to these shapes

         

        I created a simple macro that show the real name of the shape assigned form Excel and the text inside in each shapes.
        In this way we can identify the index of every shapes and , activating the edit part of the macro, assign a useful name to every shapes.

        I assigned titolo and messaggio to the shapes that will contain the text of the msgbox.

        The buttons don't necessarily need a name because I assigned 3 specific macros to them.

        I defined a function

        function msgboxShow(msgBoxTyp: msgBoxType, title: string, message: string, wb: ExcelScript.Workbook, shName: String, msgType: String, par1: string ="", par2 : string="")
        - msgBoxTyp ( one of the possibile msgbox define in an enum, showed below)
        - title - A string with the title of the msgbox
        - message - A string with the message of the msgbox
        - wb - the workbook
        - sh - the worksheet that call the msgbox
        - msgType - A string that identify the action to perform
        - par1, par2 some optional parameters for future use

        This function Unhide the Msgbox worksheet and write in some defined cells (covered by the shape) where i write the parameter of the Msgbox.

        enum msgBoxType { msgboxOk, msgboxSiNoCancel }


        The big problem is that isn't possible call one function from another macro, than i have to copy this function in every macro that need a MsgBox

        Another big problem is that the execution of a macro from a button pressing is always very very slow.
        A macro that need 1 second to run, have to wait :
        - the initialization of the button (5 seconds more or less)
        - the preparation of the button (5-10 seconds more or less)
        - the execution of the macro



Resources