[Solved] Prevent InputBox from being centered in window

[Nicco1]

Hello everyone, I have a Calc file that contains a few hundred sheets where I record daily productions (I have one sheet per day). I need to export some data to another Calc file where I will aggregate this data in a cleaner version, which I will need later for other tasks. Since the data I need to export is too difficult to filter and export automatically, I thought of a macro that would ask me for various data through input boxes and, based on the answers, copy and paste them from the current sheet to the desired file. I have a problem with the input boxes that always appear in the center of the document, covering the area where I should read the data to be entered. I tried declaring the variables positionX and positionY as Integer, assigning them a value, for example, positionX = 500, positionY = 3000, and invoking them in the InputBox string with this syntax:
question0 = UCase(InputBox("Which production do you want to record?", "PRODUCTION NUMBER", "", positionX, positionY))
but it always centers the InputBox; how can I fix this?
Below, I quote my macro if it can be helpful.

Code: Select all

Sub CompilaFoglio()
    
	
    Dim frame As Object
    Dim controller As Object
    Dim containerWindow As Object
    Dim rigaDaInserire As Integer
    Dim domanda0 As String, domanda1 As String, domanda2 As String, domanda3 As String, domanda4 As String, domanda5 As String, domanda6 As String
    Dim produzioniSheet As Object
    Dim NumProduzione As Integer
    Dim DataGiornaliero As Variant
    Dim NumGiornaliero As Variant
    Dim Miscela As String
    Dim CellaPulitura As String
    Dim CellaBagnatura as String
    Dim percorsoOriginale As String
   
    
     ' Memorizza il documento e il foglio attivi (Giornaliero)
    percorsoOriginale = ThisComponent.getURL()
    
    DataGiornaliero = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("G1").formula()
    NumGiornaliero = Mid(ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("N4").String, Len("GIORNALIERO N. ") + 1)
       
     
    
    ' Ciclo di domande 
    Do
		 ' Ripristina il foglio attivo (Giornaliero)
	
        frame = ThisComponent.CurrentController.Frame
        containerWindow = frame.ContainerWindow

        ' Porta la finestra in primo piano
        containerWindow.toFront()
            
		    domanda0 = UCase(InputBox("Quale produzione vuoi registrare?", "NUMERO PRODUZIONE"))  
		    
		    NumProduzione = domanda0
		  
		  
		    ' Assegna il valore di miscela, cella pulitura e bagnatura in base alla risposta
		    Select Case domanda0
		        Case 1
		            Miscela = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("O7").getString()
		            CellaPulitura = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("O8").getString()
		            CellaBagnatura = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("O9").getString()
		          	ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("Q43")	           						      
		        Case 2
		            Miscela = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("T7").getString()
		            CellaPulitura = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("T8").getString()
		            CellaBagnatura = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("T9").getString()
		        Case 3
		            Miscela = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("Y7").getString()
		            CellaPulitura = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("Y8").getString()
		            CellaBagnatura = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("Y9").getString()
		        Case 4
		            Miscela = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("AD7").getString()
		            CellaPulitura = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("AD8").getString()
		            CellaBagnatura = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("AD9").getString()
		        Case 5
		           Miscela = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("AI7").getString()
		           CellaPulitura = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("AI8").getString()
		            CellaBagnatura = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("AI9").getString()
		        Case 6
		            Miscela = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("AN7").getString()
		            CellaPulitura = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("AN8").getString()
		            CellaBagnatura = ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("AN9").getString()
		        Case Else
		            ' Gestisci il caso in cui la risposta non è valida
		            MsgBox "La risposta deve essere un numero da 1 a 6."
		            Exit Sub
		            End Select
		            
		  
		    domanda1 = UCase(InputBox("Inserisci il prodotto della produzione " & NumProduzione &  ":", "DESCRIZIONE PRODOTTO"))
		    domanda2 = UCase(InputBox("Inserisci la cella di destinazione:", "CELLA"))
		    domanda3 = UCase(InputBox("Inserisci la quantità :", "QUANTITÀ"))
		    domanda4 = UCase(InputBox("E' un conto lavoro? (SI/NO):", "C/LAV?"))
		
		    ' Verifica la risposta alla domanda 4
		    If UCase(domanda4) = "SI" Then
		        ' Domanda 5
		        domanda5 = UCase(InputBox("Per chi?:", "CLIENTE"))
		    End If
		
		    
		
		' Apri il foglio "Produzioni" nel file (LibreOffice)
		    Set produzioniWorkbook = StarDesktop.loadComponentFromURL("file:///home/valerio/Desktop/GIORNALIERI/ELENCO PRODUZIONI.ods", "_blank", 0, Array())
		    Set produzioniSheet = produzioniWorkbook.Sheets(0) ' Modifica il numero del foglio se necessario
		   
		    
		    ' Trova la prima riga vuota nel foglio "Produzioni"
		    rigaDaInserire = 1
		    Do While produzioniSheet.getCellByPosition(0, rigaDaInserire).String <> ""
		        rigaDaInserire = rigaDaInserire + 1
		    Loop
		
		    ' Inserimento risposte nelle celle specifiche nel foglio "Produzioni"
		    
		    produzioniSheet.getCellByPosition(0, rigaDaInserire).String = Format(DataGiornaliero, "DD/MM/YY")
		    produzioniSheet.getCellByPosition(1, rigaDaInserire).String = NumGiornaliero
		    produzioniSheet.getCellByPosition(2, rigaDaInserire).String = Miscela
		    produzioniSheet.getCellByPosition(3, rigaDaInserire).String = CellaPulitura
		    produzioniSheet.getCellByPosition(4, rigaDaInserire).String = CellaBagnatura
		    produzioniSheet.getCellByPosition(5, rigaDaInserire).String = domanda1
		    produzioniSheet.getCellByPosition(6, rigaDaInserire).String = domanda2
		    produzioniSheet.getCellByPosition(7, rigaDaInserire).String = domanda3
		    produzioniSheet.getCellByPosition(8, rigaDaInserire).String = domanda4
		    produzioniSheet.getCellByPosition(9, rigaDaInserire).String = domanda5
		   
		    ' Salva il file "ELENCO PRODUZIONI"
 			produzioniWorkbook.Store
 			produzioniWorkbook.close(true) 
		    
		    ' Domanda 6
		    domanda6 = UCase(InputBox("Hai prodotto altro? (SI/NO):", "ALTRO?"))
		Loop While domanda6 = "SI"     

   

    ' Fine del programma
End Sub

Edit: Changed subject, was InputBox Position  Make your post understandable by others  -- MrProgrammer, forum moderator

[floris v]

I tried that in Writer in LO 7.6.3.2 on Ubuntu and found that the information in https://help.libreoffice.org/latest/lo/ ... 10201.html seems to be wrong. If you put LO in a window that doesn't fill the entire screen, the dialog box is horizontally centered in the LO window, but not vertically, regardless of the x and y parameters.

[JeJe]

Create your own input box - all it is is a dialog with a textbox on it.

[MrProgrammer]

I have a problem with the input boxes that always appear in the center of the document, covering the area where I should read the data to be entered.

Instead of using InputBox, put the values of the variables for the macro in specific cells, say ZZ1, ZZ2, ZZ3, … of a sheet and get them from there. Jump to ZZ1 using the Name Box. Put your values in the cells and run the macro. You can put formulas in the cells to help. For example, if you want parameter ZZ1 to contain the value that is cell D5, instead of typing that value in ZZ1, use formula =D5.