Need to convert VBA to Openoffice basic

I have this code that works great on an excel worksheet but I need to convert it openoffice calc

Sub KeyCellsChanged()
   Dim Cell As Object
   ' If the values in A11:C11 are greater than 50...
   For Each Cell In Range("C3:E10")
    Dim exitSubFlag As Boolean
    exitSubFlag = False
    If Range("C4").Value = "2for promo" Then
        If Range("C9").Value > 80 Then
            foo = MsgBox("Frame Price can NOT exceed $79.99 when a 2for Promo is selected.", vbOKOnly, "Validation violation")
                If vbOK Then
                Range("C9").Value = 0

                End If
            exitSubFlag = True
        End If
     End If

    If exitSubFlag Then
        Exit Sub
    End If
  Next Cell

End Sub

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This looks as if it could run under OpenOffice without many changes but it's nevertheless senseless code.
Why is there an iteration over Cells where Cell is never used. So what good does this code? I suggest you send a proper spreadsheet with this code which actually does something with sens. I've removed the senseless loop and got to this:

Ok agreed does not make sense but that's the same with you Excel VBA code.
Dim Doc As Object
Dim Sheet As Object

Sub KeyCellsChanged()
   ' If the values in A11:C11 are greater than 50...
   dim exitSubFlag as Boolean
   dim cellA9 as Object
   dim cellC4 as Object
   dim wCell
   dim foo
   const intOkButton as Integer = 1
   const intStopIcon as Integer = 16
   Doc = ThisComponent
   Sheet = doc.Sheets(0)
    cellc9 = Sheet.getCellRangeByName("C9")
   	cellc4 =  Sheet.getCellRangeByName("C4")
    If cellc4.Formula = "2for promo" Then
        If cellc9.Value > 80 Then
            foo = MsgBox("Frame Price can NOT exceed $79.99 when a 2for Promo is selected.", intStopIcon + intOkButton , "Validation violation")
                If foo = intOkButton Then
                cellC9.Value = 0

                End If
            exitSubFlag = True
        End If
     	msgBox "Useless comemnt"
     End If

end sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
4isteamAuthor Commented:
Thanks frido,

cell C4 is a drop list and C9 the user enters a value. what I'm trying to acomplish, and it is working fine on my excel spreadsheet, is that any time the value in any of those to cells changes the validation is checked. if cell C4 selection is "2for promo" then the value in C9 can't be greater than $79.99. but sometimes the user might select a "% discount" on cell C4 enter a value greater than $79.99 but then goes back to cell C4 and changes it to "2for promo" then I need the validation to run again.
Well that was absolutly unknow to me. So again if you want something running on OpenOffice you better provide at a working Excel worksheet otherwise, no-one will be able to help you.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

4isteamAuthor Commented:
Thanks again fridom,

The code you provided works fine. The one thing the isee you commented out is setting the focus on C9 after updating to 0. How can I implement the set focus?
Use something along this lines
controller = Doc.getcurrentController

4isteamAuthor Commented:
Thanks fridom,

That worked great. you wouldn't know how to get this macro to run on startop. I created it in the document's Macro library but i have to manually run it for it to work. I need it to start running the moment I open the spreadsheet.

Menu Extras modify/change (in German it is Extras -> Anpassen directly over optoins. I do not know the name
in English. Then go to the tab Events and choose the proper time to run the makro e.g On Load or the like
klick on Macro and choose the name you've given to the Makro.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.