[Solved] Macro for a button to fill text in formula

[WNT2000]

Hi
I am new to this but I have spent more than a day to find a solution on how to write this VBA code in Basic:

Code: Select all

Private Sub CommandButton1_Click()

Rem check which value control cells to move
 If Range("'Ark1'!A11").Value = "Hulmur" Then
 Application.EnableEvents = False
Rem this is my moving cells macro
 Call Flyt_Hulmur
 Application.EnableEvents = True
 Else
Rem check which value control cells to move
 If Range("'Ark1'!A11").Value = "Loftsrum" Then
 Application.EnableEvents = False
Rem this is my moving cells macro
 Call Flyt_Loftsrum
 Application.EnableEvents = True
 Else
Rem check which value control cells to move
 If Range("'Ark1'!A11").Value = "Krybekælder" Then
 Application.EnableEvents = False
Rem this is my moving cells macro
 Call Flyt_Krybekælder
 Application.EnableEvents = True
 End If
 End If
 End If
Rem All actions on sheet 3 therefore - return to sheet 1
 Sheets("Ark1").Select
End Sub

this script helps my Excel sheet to choose the right piece of text after doing some calculations. I execute this code pressing a button and I want to do the same with a basic code in my calc spreadsheet.

What I cannot figure out is how to write the same code in basic so I can use a button to do the same job in my OO calc spreadsheet.

I add a sample spreadsheet with macros moving cells dependent on the value of a specific cell just to give you the Picture of what is missing.
Namely the code that will move text depending on value of cell.

[Villeroy]

Whtever "Flyt_Hulmur" and "Flyt_Krybekælder" do, I'm 90% sure that you do not need a single line of macro code.

[WNT2000]

The macros find a piece of text including formulas and copy them to another cell on the same sheet so my printout of the page have the right information for the recipient.

the controlling string from my attached spreadsheet is the letters v,x,y,z that shall control which numbers should be moved to an other cell for calculation.

Hope somebody see the Picture and can give me directions.

[MTP]

I looked at the macro, and looked at the spreadsheet, and I have very little idea of what you need overall.

I may be able to help in small steps. At the start of the macro, it compares a range with a value. I don't think StarBasic will do this - instead, you have to compare just one cell to the value, or step through the range array and compare each value to each part of the array one at a time.

I do not understand why "enable events" is turned off and on. What events are expected to interfere with the macro?

Lastly, for now, the post is missing some of the macro code. It looks like there is more code for sub Flyt_Hulmur, sub Flyt_Loftsrum, and sub Flyt_Krybekælder. You will also need to transfer the code for all these into StarBasic.

[WNT2000]

It is of course a sample to show what technique I am searching for.

the list of letters will control which line has to be copied (letter v Means call a macro to copy line 1, letter x Means call an other macro to copy line 2, etc.)

exactly what name my macros have does not matter in this case, only thing I am searching for, is the technique to call a macro depending of value of the cell with choosable letters. And because this is not a recordable macro but one I have to write myself I am stuck.

the VBA does exactly what I want in Excel with data on sheet 1 and my text to be copied on sheet 3. I have made the VBA as an If, Then Else programme so it just chooses the right macro according to the value of Sheet1 cell A11 which is a list of 4 values to choose.

[MTP]

Maybe this?

Code: Select all

oDoc = ThisComponent
oSheets = oDoc.getSheets()
oSheet = oSheets.getSheetByName("The name of your sheet")
oCell = oSheet.getCellByPosition(column, row) 'A1 is 0,0 - A2 is 0,1 - B1 is 1,0
If oCell.getString() = "V" Then 
   Flyt_Hulmur()
Else
   If oCell.getString() = "X" Then 
      Flyt_Loftsrum()
   Else
      If oCell.getString() = "Z" Then
         Flyt_Krybekælder()
      End If
   End If
End If

There is a lot of good information on StarBasic at Andrew Pitonyak's site

[WNT2000]

Thank you very much for your help.

I am sure I can get this to Work when I get non-English letters out of code.