Hello,
I am trying to get a macro to run on any selected worksheet within my workbook.
FJCC kindly provided me with a routine to solve my last issue (Buttons.ods) but it only works on sheet1 specified in the macro.
I need load a new worksheet every week and have the macro work regardless of the sheet name.
Thanks in advance - much appreciated.
I have attached the basic working file.
Windows 10.
OO 4.1.7
[Solved] Run macro across all worksheets within a workbook
[Solved] Run macro across all worksheets within a workbook
- Attachments
-
- 20200101_Trent.ods
- (40.69 KiB) Downloaded 173 times
Last edited by Kalysta on Sat Nov 02, 2019 2:50 pm, edited 1 time in total.
Windows 10
Open Office 4.1.7
Open Office 4.1.7
Re: Using a macro across all worksheets within a workbook.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Re: Using a macro across all worksheets within a workbook.
In FJCC's solution Buttons.ods the macro is hardcoded to use Sheet1
Let it use the ActiveSheet instead
Let it use the ActiveSheet instead
Code: Select all
Sub Button(oEv)
Lab = oEv.Source.Model.Label
Col = oEv.Source.Model.BackgroundColor
CurrSelec = ThisComponent.CurrentSelection(0)
IF Not CurrSelec.supportsService("com.sun.star.sheet.SheetCell")Then
msgbox("Inappropriate Action")
exit sub
End If
IF CurrSelec.CellAddress.Column <> 1 Then
msgbox("Inappropriate Action")
exit sub
End If
rem oSheet = ThisComponent.Sheets.getByName("Sheet1") 'originally coded by FJCC
oSheet = ThisComponent.getCurrentController.getActiveSheet()
CurrSelec.String = Lab
oRow = oSheet.getCellrangeByPosition(1, CurrSelec.CellAddress.Row, 17, CurrSelec.CellAddress.Row)
oRow.CellBackColor = Col
oNextCell = oSheet.getCellByPosition(1, CurrSelec.CellAddress.Row + 1)
ThisComponent.CurrentController.select(oNextCell)
End Sub
- Attachments
-
- Buttons_activesheet.ods
- (12.99 KiB) Downloaded 204 times
____________
DiGro
AOO 4.1.15 (Dutch) on Windows 11. Scanned with Ziggo Safe Online (F-Secure)
DiGro
AOO 4.1.15 (Dutch) on Windows 11. Scanned with Ziggo Safe Online (F-Secure)
Re: Using a macro across all worksheets within a workbook.
I would dissuade from the one-spreadsheet-per-week (OSSPW) design.
Why not collect and maintain the raw data with low formatting, and create presentations and reports for formatted prettyprint containing one week (or one month or any range of dates) from a respective selection in specialised sheets?
Ideally you then need one data sheet and one sheet for presentation, charting/visualising and whatever... (probably a few sheets optimised under different aspects). This is partly how a database design would do it, and if there are additional requirements a database solution should anyway be preferred.
In addition you may create reports not youst including a number of subsequent days, but also something like "all the sundays in my given range" from the same single data sheet.
In many cases the needed selection will be supported by the filtering tools AOO and LibO come with. If needed very often a bit of programming can rather easily enhance the filtering and reduce manual handling. One day, if your data scaled up too much, you also can easily export them to a real database.
With OSSPW you never can avoid this fundamental problem:
If need arises to change parts of the functionality or the arrangements of cells containing some of the data, you will have lots of trouble to do that for all the weekly sheets. Even if you are ready to expend the time, the reworking will need lots of manual interaction, and will be very error-prone therefore. What if you need to add a source, to exchange an old one for a new one, or just to rectify the spelling of one?
Why not collect and maintain the raw data with low formatting, and create presentations and reports for formatted prettyprint containing one week (or one month or any range of dates) from a respective selection in specialised sheets?
Ideally you then need one data sheet and one sheet for presentation, charting/visualising and whatever... (probably a few sheets optimised under different aspects). This is partly how a database design would do it, and if there are additional requirements a database solution should anyway be preferred.
In addition you may create reports not youst including a number of subsequent days, but also something like "all the sundays in my given range" from the same single data sheet.
In many cases the needed selection will be supported by the filtering tools AOO and LibO come with. If needed very often a bit of programming can rather easily enhance the filtering and reduce manual handling. One day, if your data scaled up too much, you also can easily export them to a real database.
With OSSPW you never can avoid this fundamental problem:
If need arises to change parts of the functionality or the arrangements of cells containing some of the data, you will have lots of trouble to do that for all the weekly sheets. Even if you are ready to expend the time, the reworking will need lots of manual interaction, and will be very error-prone therefore. What if you need to add a source, to exchange an old one for a new one, or just to rectify the spelling of one?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Using a macro across all worksheets within a workbook.
I'm indebted to you who replied so promptly. I am working another project at the moment... (Grandkid's Christmas)
My initial view of the solution is that it looks good for my application. I'll apply it tomorrow and close the thread.
Thanks again.
K
My initial view of the solution is that it looks good for my application. I'll apply it tomorrow and close the thread.
Thanks again.
K
Windows 10
Open Office 4.1.7
Open Office 4.1.7