View original (French)

[macro openoffice] programming a msgbox

Solved
Didier61 -  
<ccm>hello everyone
I am starting with macOS on OOo 2.0.4 but I’m a beginner. In Calc on the first sheet, I have placed buttons that allow me to display one of the ten other sheets. Once on the desired sheet, I have several buttons that allow me to print a selection. I can’t find solutions for the following problems:
1) The recorder takes into account the sheet number in the macro to make it appear, but I would like to use the sheet name.

2) I am unable to insert a msgbox with two buttons, ok and cancel, to ask a question and confirm the printing or cancel it.
thank you for your help
Didier
Configuration: Windows Xp - OOo 2.0.4

5 answers

  • Votes
  • Date
  • Descending date
  1. lami20j Moderator
    Hello,

    here is the code for msgbox
    REM ***** BASIC ***** Sub Main dim repMsgBox as String '0 - only ok '1 - ok + cancel '2 - interrupt + retry + cancel '3 - yes + no + cancel '4 - yes + no '5 - retry + cancel '6 - ok 'the icons '0 none '16 critical message X '32 Question ? '48 Warning ! '64 Information i 'default button choice '0 the first '256 the 2nd '512 the last 'the message type is the sum of 3 values chosen 'For example: type 3 with question and the 2nd by default '3 + 32 + 256 = 291 repMsgBox=msgbox("Hello, I am MsgBox",291) 'here I retrieve the return of msgbox msgbox repMsgBox End Sub
    lami20j
    1. lami20j Moderator
      Re, 

      REM ***** BASIC ***** Sub Main ' the msgbox returns a value depending on the button you use ' 1 for ok ' 2 for cancel ' it's up to you to discover the rest dim repMsgBox as String repMsgBox=msgbox("Hello, I am MsgBox",1) 'now we will use the return of the msgbox 'so depending on the button used we execute something if repMsgBox=1 then msgbox "You clicked OK" elseif repMsgBox=2 then msgbox "You clicked CANCEL" end If End Sub
      And in your case
      REM ***** BASIC ***** Sub Main ' the msgbox returns a value depending on the button you use ' 1 for ok ' 2 for cancel ' it's up to you to discover the rest dim repMsgBox as String repMsgBox=msgbox("Do you want to continue?",1,"Didier61 Macro") 'now we will use the return of the msgbox 'so depending on the button used we execute something if repMsgBox=1 then oDocument = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") dim args1(0) as new com.sun.star.beans.PropertyValue args1(0).Name = "ToPoint" args1(0).Value = "$B$26:$R$43" dispatcher.executeDispatch(oDocument, ".uno:GoToCell", "", 0, args1()) dim args2(2) as new com.sun.star.beans.PropertyValue args2(0).Name = "Copies" args2(0).Value = 1 args2(1).Name = "Selection" args2(1).Value = true args2(2).Name = "Collate" args2(2).Value = false dispatcher.executeDispatch(oDocument, ".uno:Print", "", 0, args2()) elseif repMsgBox=2 then msgbox "Execution was cancelled.",64,"Didier61 Macro" exit sub end If End Sub
      lami20j
      1. Didier61 Author
        Thank you lami20j for responding so quickly, but my problem is not entirely solved. This is the beginning of my macro

        sub Imp1
        dim repMsgBox as string
        repMsgBox=msgbox("Do you want to print this report?",289)

        but what functions should follow so that when I click the cancel button it exits the macro and if I click ok it continues with my macro below

        oDocument = ThisComponent.CurrentController.Frame
        dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
        dim args1(0) as new com.sun.star.beans.PropertyValue
        args1(0).Name = "ToPoint"
        args1(0).Value = "$B$26:$R$43"
        dispatcher.executeDispatch(oDocument, ".uno:GoToCell", "", 0, args1())
        dim args2(2) as new com.sun.star.beans.PropertyValue
        args2(0).Name = "Copies"
        args2(0).Value = 1
        args2(1).Name = "Selection"
        args2(1).Value = true
        args2(2).Name = "Collate"
        args2(2).Value = false
        dispatcher.executeDispatch(oDocument, ".uno:Print", "", 0, args2())
        End sub

        Thank you
        1. Didier61 Author
          Thanks again to lami20j, it works well and the macro is as follows:

          sub Imp1
          dim repMsgBox as string
          repMsgBox=msgbox("desired text", sum of the 3 chosen numbers, "possibly msgbox name")
          if repMsgBox=1 then

          (text of the macro we want to execute or not)

          elseif repMsgBox=2 then
          exit sub
          end If
          end sub
          2. -pyer
            Hello,
            I would like to know how to program the buttons to navigate between the different sheets of the workbook. I am migrating from Lotus 1-2-3 to Calc and cannot find this feature.
            Thank you
            -pyer
            1. Didier61 Author
              Good evening -pyer
              On a spreadsheet, you place a button for each of the sheets you want to access and you associate each button with a macro as indicated below:

              dim myDocument As Object, theSheets As Object, mySheet As Object, myCell As Object

              Sub choice1 'choice 1 corresponds to the name of your macro
              myDocument = thisComponent
              theSheets = myDocument.Sheets
              mySheet = theSheets.getByName("sheet1") 'in quotes the name of the sheet you want to get
              myCell = mySheet.getCellRangeByName("D5") 'the address of the cell you want to select eventually
              myDocument.currentController.Select(myCell)
              End sub
              Didier61