?
Solved

Need to convert VBA to Openoffice basic

Posted on 2010-09-22
7
Medium Priority
?
1,590 Views
Last Modified: 2013-12-27
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
                'Range("C9").Activate

                End If
            exitSubFlag = True
        End If
           
            Else
           
     End If
   

   
    If exitSubFlag Then
        Exit Sub
    End If
  Next Cell
 

End Sub

Thanks,
0
Comment
Question by:4isteam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 24

Accepted Solution

by:
fridom earned 1000 total points
ID: 33743438
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
                'Range("C9").Activate

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

   
   
end sub

Open in new window

0
 

Author Comment

by:4isteam
ID: 33744920
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.
0
 
LVL 24

Expert Comment

by:fridom
ID: 33745410
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:4isteam
ID: 33745488
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?
0
 
LVL 24

Expert Comment

by:fridom
ID: 33751566
Use something along this lines
controller = Doc.getcurrentController
controller.select(cellc9)

0
 

Author Comment

by:4isteam
ID: 33757414
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.

0
 
LVL 24

Expert Comment

by:fridom
ID: 33759565
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.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question