[Solved] I need each button click to do a different thing.

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

[Solved] I need each button click to do a different thing.

Post by imdumb »

I have a project that's at a standstill, until I can solve this problem. Basically I need a button that will do the following:

On the first click of the button:

1. Copy whats in Cell A1.
2. Then paste that into Cell B1.

Then later, on the second click of the same button:

1. Copy whats in cell A2.
2. Then paste that into cell B2.

Then on the third click of the same button:

1. Copy whats in cell A3.
2. Then paste that into cell B3.

And then be able to continue to repeat that process over and over.

What would really be great is, if you know of a way, would be to, at the end of a certain number of clicks of the button (lets say, about 50), have this go back and delete the contents of Column B, but that would really be a super bonus. What I really need right now is how to do what I mentioned above.

Thanks!
Last edited by imdumb on Sat Oct 14, 2017 6:45 am, edited 1 time in total.
open office 4.1.2, windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: I need each click of a button to do something different.

Post by FJCC »

I did this by using cell C1 to store a count value. This could be any cell. I also wrote the code for rows 1 through 12 because I didn't want to click the button 50 times. I put in comments to show how to adjust the code to work over 50 rows.

Code: Select all

Sub Main
oSheet = ThisComponent.Sheets.getByName("Sheet1")
CountCell = oSheet.getCellrangeByName("C1") 'change C1 to whatever you want
CountVal = CountCell.Value
If CountVal < 12 then 'change the 12 to 50
  CopyCell = oSheet.getCellByPosition(0, CountVal)
  PasteCell = oSheet.getCellByPosition(1, CountVal)
  oSheet.copyRange(PasteCell.CellAddress, CopyCell.RangeAddress)
  CountCell.Value = CountCell.Value + 1
else
  ClearRange = oSheet.getCellrangeByName("B1:B12") 'change to B1:B50
  ClearRange.clearContents(7)
  CountCell.value = 0
End If
End Sub
Attachments
SequentialCopy.ods
(10.7 KiB) Downloaded 134 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
janfl
Posts: 29
Joined: Thu Sep 28, 2017 4:17 pm

Re: I need each click of a button to do something different.

Post by janfl »

Something like this:

Code: Select all

On click:
Get click_counter from counter_location
IF click_counter=50
click_counter=1
counter_location=1
store click_counter to counter_location
else
click_counter=click_counter+1
store click_counter to counter_location
ENDIF
Select Case click_counter
  Case click_counter=1
    StatementBlock1
  Case click_counter=2
    StatementBlock2
  Case Else
    StatementBlock3
End Select
Last edited by janfl on Fri Oct 13, 2017 7:23 pm, edited 1 time in total.
Open Office 4.1.3 Windows 10
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: I need each click of a button to do something different.

Post by imdumb »

Wowee. I can't believe I could get an answer like that that simply and so fast. Amazing! (FJCC's answer, I havn't tried Janfl's)

Another thing if you don't mind.

How could I change that code to do this:

A) With one button click copy 3 or more cells (lets say cells A10, B10 and C10) and then paste them into 3 cells (lets say cells BA10, BB10 and CC10) (instead of only copying and pasting just one cell) and then, at the end of the run, having it go back and delete columns AA, BB and CC rather than just Column B? (Everything else would run the exact way you set it up). (I actually have rows of about 50 cells that this needs to be done with)

B) Also, how could I set this up with a "paste special" rather than just a simple "paste", one that pastes in only the numbers and text but no formulas or anything else?

I figured that if I got the simple version above I could change it to do these things but I can't figure it out.

Thank you for this. It's tremendous!
open office 4.1.2, windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: I need each click of a button to do something different.

Post by FJCC »

Your last post is confusing because you say the paste columns are BA, BB and CC(?) and then later you mention AA, BB and CC. I assumed you wanted to paste into contiguous cells, so I chose BA, BB and BC. The code starts in row 1 and goes to row 12, as before. I moved the cell for counting button clicks to E1

Code: Select all

oSheet = ThisComponent.Sheets.getByName("Sheet1")
CountCell = oSheet.getCellrangeByName("E1")
CountVal = CountCell.Value
If CountVal < 12 then
  CopyRange = oSheet.getCellrangeByPosition(0, CountVal, 2, CountVal) 'Column A = 0, B = 1 etc
  PasteRange = oSheet.getCellrangeByPosition(52, CountVal, 54, CountVal) 'Column BA = 52
  PasteRange.DataArray = CopyRange.DataArray
  CountCell.Value = CountCell.Value + 1
else
  ClearRange = oSheet.getCellrangeByName("BA1:BC12")
  ClearRange.clearContents(7)
  CountCell.value = 0
End If
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: I need each click of a button to do something different.

Post by imdumb »

Sorry, your right, I messed that up.

It should have been delete cells "BA, BB and CC," just as you mentioned. You got it right.

I'll try out your code a little later and I'll let you know how it goes!
open office 4.1.2, windows 7
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: I need each click of a button to do something different.

Post by imdumb »

FJCC, your second example works and works beautifully. My project has new life breathed into it, and I think stands a pretty chance of getting completed.

Thank you. Your a hero! :bravo:
open office 4.1.2, windows 7
Post Reply