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!
[Solved] I need each button click to do a different thing.
[Solved] I need each button click to do a different thing.
Last edited by imdumb on Sat Oct 14, 2017 6:45 am, edited 1 time in total.
open office 4.1.2, windows 7
Re: I need each click of a button to do something different.
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: I need each click of a button to do something different.
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
Re: I need each click of a button to do something different.
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!
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
Re: I need each click of a button to do something different.
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: I need each click of a button to do something different.
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!
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
Re: I need each click of a button to do something different.
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!
Thank you. Your a hero!
open office 4.1.2, windows 7