Page 1 of 1

[Solved] Run macro across all worksheets within a workbook

PostPosted: Sun Oct 27, 2019 1:49 pm
by Kalysta

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

Re: Using a macro across all worksheets within a workbook.

PostPosted: Sun Oct 27, 2019 2:37 pm
by JeJe

Re: Using a macro across all worksheets within a workbook.

PostPosted: Sun Oct 27, 2019 3:29 pm
by DiGro
In FJCC's solution Buttons.ods the macro is hardcoded to use Sheet1

Let it use the ActiveSheet instead

Code: Select all   Expand viewCollapse view
Sub Button(oEv)
Lab = oEv.Source.Model.Label
Col = oEv.Source.Model.BackgroundColor

CurrSelec = ThisComponent.CurrentSelection(0)

IF Not CurrSelec.supportsService("")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)
End Sub

Re: Using a macro across all worksheets within a workbook.

PostPosted: Sun Oct 27, 2019 10:56 pm
by Lupp
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?

Re: Using a macro across all worksheets within a workbook.

PostPosted: Tue Oct 29, 2019 4:43 pm
by Kalysta
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.