[Solved] Run macro across all worksheets within a workbook

[Kalysta]

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

[JeJe]

viewtopic.php?f=45&t=21035

[DiGro]

In FJCC's solution Buttons.ods the macro is hardcoded to use Sheet1

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

[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?

[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.

K