[Solved] Run a macro in a button in a spreadsheet

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
wow_i_had_no_idea
Posts: 5
Joined: Fri Jun 18, 2010 6:21 am

[Solved] Run a macro in a button in a spreadsheet

Post by wow_i_had_no_idea »

I see that the house rules call for posting one question per thread. I'm just wrapping my head around OO Calc, super excited about the flexibility, but pretty lost as far as HOW to get the functionality I'm looking for.

The first question is: I want to create a Macro (found a GUI tutorial on that here) that copies a data set to another location in the same sheet, filters it, then sorts it. I'm still tinkering with it, but I think I'll get that part eventually (maybe...)

And I found another tutorial that would allow me to add that macro to a toolbar, a keystroke, or a context menu. But I can't yet figure out how to assign the Macro to a cell, such that I can simply click on that cell (effectively, a button within the spreadsheet).

I'm sure it's right in front of me, probably the simplest thing ever to do...
 Edit: OK, turns out if you have a "how do I" question, check out the code snippets board.
Someone posted a great example, I'm sure I'll be back with questions related to how to make it work:

http://user.services.openoffice.org/en/ ... =21&t=1254

*Update: here is a step by step: http://user.services.openoffice.org/en/ ... 86#p121430 
**Update: 4/9/11 - I never managed to get the above code working in my file, using the macro recorder, I am not able to get the button to launch the macro. I've found other threads talking about this, but not using the macro recorder (I am so far useless with code :-| ) I'll repost below where I am with this.
Last edited by wow_i_had_no_idea on Sun Apr 10, 2011 12:17 pm, edited 4 times in total.
OpenOffice 3.2.0 with MacOS 10.5.6
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: run a macro by clicking on a cell

Post by RoryOF »

Look at Andrew Pitonyak's site on OpenOffice macros
http://www.pitonyak.org/oo.php
If what you want is possible, the information is most probably in his writings.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
wow_i_had_no_idea
Posts: 5
Joined: Fri Jun 18, 2010 6:21 am

Re: Run a macro in a button in a spreadsheet

Post by wow_i_had_no_idea »

the mother lode. Although this looks to be a great reference (thanks) he glosses over the specific functionality I am looking for - under section 2.7 "Run a Macro", he simply states: "Add a button to your a document or toolbar that calls the macro. "
The next bullet point is about assigning a Macro to a Keystroke, etc. The fact that he glosses over it must mean it is forehead smacking simple. But the question remains: "is it possible to run a macro simply by clicking on a cell? If so, how?"
Last edited by wow_i_had_no_idea on Sun Apr 10, 2011 12:17 pm, edited 1 time in total.
OpenOffice 3.2.0 with MacOS 10.5.6
wow_i_had_no_idea
Posts: 5
Joined: Fri Jun 18, 2010 6:21 am

Re: Run a macro in a button in a spreadsheet

Post by wow_i_had_no_idea »

OK, here is what I have learned, spelled out in the most basic terms, no code involved. In my case, I have 5 columns and like 200 rows. I want to quickly have a specific view of my data, without having to go to data->sort and plug in the criteria each time. Since I upload my files to GDocs and view on my phone, I want the outputted data on their own sheets. So here we go:

Step 1: define your data range. Select the top row, including the column headers, and hold shift and click the cell of the bottom right-most cell so you've got ALL your data selected. Data->Define Range. Give it a name (no spaces, underscores)
Step 2: define your output (if you want to make a copy of your original data and dump the new sorted data in another sheet, which I did) If so, create a new sheet, name it something without spaces or underscores. click on the first cell where you want the data copied. Again, define range, give it a name. OK, so now you're ready for...
Step 3: record your macro. So I clicked Tools->Macros->Record Macros. Then I went through the motions to get my correct filter. (for filtering, go to data->filtering->autofiltering, set the criteria, and click on "More" below to copy the output elsewhere, select the range you created before for your output) Same process for sorting, or a combination of the two.
Step 4: Verify that your sort/filter worked, if needed go to the sheet you ouputted your data.
Step 5: go back to your original sheet, and click Data->Refresh Range
Step 6: (Optional) if you want the button to take you automatically to your new sort (ie new sheet) then go back to where your data was outputted and click on any cell there
Step 7: Click "Stop Recording"
Step 8: Name the Macro. Now test the macro by Tools->Macro->Run Macro. If that doesn't work, you missed something.
Step 9: Make a button - View->Toolbars->Drawing. You should get another toolbar visible, mine popped up in the bottom of the screen. click on the rectangle (or ellipse, I guess) and your cursor will turn into a crosshair. Find a cell you want the button to reside in (make sure it's outside of your data range!) Make a rectangle. POW! the beginnings of a button.
Step 10: Assign the Macro to your button: View->Toolbars->Form Design and click on the little pencil/triangle icon (toggle Design mode On). Now right click on your button, and click on "Assign Macro..." choose the macro you created
Step 11: Toggle off the Design mode, and click your button. you should have been able to get the results, I think.

I hope this helps another noob, it took me 8 months to figure this out, at my snail's pace. Good luck! (feel free to PM me if this thread gets closed when I mark it "solved")
OpenOffice 3.2.0 with MacOS 10.5.6
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Re: [Solved] Run a macro in a button in a spreadsheet

Post by Mohawk »

wow_i_had_no_idea wrote:OK, here is what I have learned, spelled out in the most basic terms, no code involved. In my case, I have 5 columns and like 200 rows. I want to quickly have a specific view of my data, without having to go to data->sort and plug in the criteria each time. Since I upload my files to GDocs and view on my phone, I want the outputted data on their own sheets. So here we go...
I realise this is archaeology, but thanks for posting. I ran into exactly the same problem: the text hinted it could be done, but gave no clue how.
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
Post Reply