[Solved] Calc Macro to move from one sheet to another

[Robitron]

Okay, I've been pouring through the websites trying to find how to do it but haven't found any documentation.

I need my existing macros to automatically switch from one sheet to the next before continuing with its process. I tried to use the record macro and insert the macro into my other ones at the right places but I get strange occurrences that way. So I really need to understand how to write the code myself so that I can modify it as needed.

If anyone has any links to docs that cover this topic or other types of help, I'd love to have them!

Thanks in advance!

[JohnV]

This code will change sheets from low numbers the higher ones. If you remove the "+2" from the first line then it will move in the opposite direction.

Code: Select all

sub ChangeSheet
s = ThisComponent.CurrentSelection.CellAddress.Sheet+2
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = s
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
end sub

[Robitron]

This code will change sheets from low numbers the higher ones. If you remove the "+2" from the first line then it will move in the opposite direction.

Code: Select all

sub ChangeSheet
s = ThisComponent.CurrentSelection.CellAddress.Sheet+2
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = s
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
end sub

Thanks for the input! So this looks like the macro that was made by the Record Macro option. So I have a few questions.

First, running this macro creates and error "Basic Runtime Error ; Property or Method not found: Cell Address." So does this mean that it needs a
Dim Cell Address as Object line or something?
Second, will this macro jump to a specific sheet in the document from within another macro?
Example: I've created a sheet called Journal in which the macro (through InputBoxes) asks a series of questions ; Date, Item and Quantity
So what I need is for the macro to jump to the Journal page, ask for the relevant info, ask to confirm info, add the info to the journal and finally jump
back to the main page again.
Third, from what I've read, the dispatcher isn't really necessary. Is this true?

Here's a mock-up of how the macro currently runs. As you can see, the data is being listed on the Journal Sheet, however, if you are not currently on the Journal Page, you can not see if the data posted correctly.

[JohnV]

from what I've read, the dispatcher isn't really necessary. Is this true?

Yes, here is another way to change the active sheet.

Code: Select all

Sub ChangeActiveSheet
oSheet = ThisComponent.Sheets.getByName("Journal")
ThisComponent.CurrentController.Select(oSheet)
End Sub

[Robitron]

sweet! That's a lot simpler! Thanks Loads!!!