## 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 parts

x = 9
y = 7

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 - 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 used
REM Private Sub Worksheet_change(ByVal Target As Range)
REM OK, so what document are you using? I will assume
REM 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