## Help converting excel Macro

Creating a macro - Writing a Script - Using the API

### Help converting excel Macro

Hello I am new to Openoffice.org basic and I cannot figure out how to convert my code that I wrote for Excel to OOO. Any help would be helpful.

My macros should do the following. When you enter a number, it should bring up msgbox that says sure right qty and part used yes/no, if yes then recalculates, (auto calc is off)

Here is my excel macros i for used parts and one for recieved

Code: Select all   Expand viewCollapse view
`Private Sub Worksheet_change(ByVal Target As Range)    'used partsx = 9y = 7Do    z = Worksheets("sheet1").Cells(x, y)        x = x + 1    Loop Until z > 0 Or x = 3500        If z > 0 Then                currentitem = Worksheets("sheet1").Cells(x - 1, y - 3)        currentqty = Worksheets("sheet1").Cells(x - 1, y)        currentdesc = Worksheets("sheet1").Cells(x - 1, y - 2)                If IsNumeric(currentqty) = True Then                    response = MsgBox("Did you use (" & currentqty & ") MANF. # " & currentitem & vbCrLf & vbCrLf & currentdesc & "?", vbYesNo)                    If response = vbYes Then                        Calculate                Worksheets("sheet1").Cells(x - 1, y) = ""                                          response = MsgBox("You have successfully updated your inventory list. Do you need to Use/Recieve any more items?", vbYesNo)                                        If response = vbNo Then                        ActiveWorkbook.Save                        ActiveWorkbook.Close                    End If                                End If            If response = vbNo Then                Worksheets("sheet1").Cells(x - 1, y) = ""            End If                                Else            MsgBox "You must enter a number!!", vbOKOnly            Worksheets("sheet1").Cells(x - 1, y) = ""                    End If            End If            'recieved parts x = 9 y = 9 Do    z = Worksheets("sheet1").Cells(x, y)        x = x + 1    Loop Until z > 0 Or x = 3500        If z > 0 Then        currentitem = Worksheets("sheet1").Cells(x - 1, y - 5)        currentqty = Worksheets("sheet1").Cells(x - 1, y)        currentdesc = Worksheets("sheet1").Cells(x - 1, y - 4)                If IsNumeric(currentqty) = True Then            response = MsgBox("Did you recieve (" & currentqty & ") MANF. # " & currentitem & vbCrLf & vbCrLf & currentdesc & "?", vbYesNo)            If response = vbYes Then                        Calculate                Worksheets("sheet1").Cells(x - 1, y) = ""                response = MsgBox("You have successfully updated your inventory list. Do you need to Use/Recieve any more items?", vbYesNo)                    If response = vbNo Then                        ActiveWorkbook.Save                        ActiveWorkbook.Close                    End If                                End If                If response = vbNo Then                    Worksheets("sheet1").Cells(x - 1, y) = ""                End If                                    Else                MsgBox "You must enter a number!!", vbOKOnly                Worksheets("sheet1").Cells(x - 1, y) = ""        End If          End If    End Sub`
kdsmds

Posts: 1
Joined: Thu Feb 21, 2008 6:01 am

### Re: Help converting excel Macro

I edited your post to place your code into a code block. Does this little snippet get you started?
Code: Select all   Expand viewCollapse view
`REM The argument "Target" is never usedREM Private Sub Worksheet_change(ByVal Target As Range)REM OK, so what document are you using? I will assumeREM the current document.Sub Worksheet_change()'used parts  Dim x As Long  Dim y As Long  Dim z As Double  Dim oSheet    x = 9  y = 7  oSheet = ThisComponent.Sheets.Sheet1  Do    REM This is NOT efficient, but it is a direct translation.    REM I do not know if x is row or column, so I assumed that    REM x is the row...    'z = Worksheets("sheet1").Cells(x, y)    z = oSheet.getCellByPosition(y, x).Value    x = x + 1  Loop Until z > 0 Or x = 3500  If z > 0 Then           'currentitem = Worksheets("sheet1").Cells(x - 1, y - 3)    'currentqty = Worksheets("sheet1").Cells(x - 1, y)    'currentdesc = Worksheets("sheet1").Cells(x - 1, y - 2)    currentitem = oSheet.getCellByPosition(y-3, x-1).Value    currentqty = oSheet.getCellByPosition(y, x-1).Value    currentdesc = oSheet.getCellByPosition(y-2, x-1).Value`
Andrew Pitonyak
http://www.pitonyak.org/oo.php
LO and AOO on Fedora
pitonyak
Volunteer

Posts: 186
Joined: Sun Oct 07, 2007 9:13 pm
Location: Columbus, Ohio, USA