Scroll in the sheet pane with the keyboard, possible?

Question

Level 1

29 points

Scroll in the sheet pane with the keyboard, possible?

I have document that contains about 40 sheet. Therefore I would like to be able to scroll in the sheet pane with the keyboard. However, I haven't found a way. Command - Page up and Command - Page down select previous and next sheet. Command - home and Command - end select first and last sheet. But the scrollbar remains in its position.

If someone could give me an applescript that took the scrollbar in the sheet pane to home and end, would that be apprecited.

Fred

iMac, Mac OS X (10.6.8)

Posted on Feb 12, 2013 8:26 AM

Reply

Answer 1

Badunit

Level 7

26,006 points

Feb 13, 2013 7:14 AM in response to fred6633

In this case it was simply my reading skills. I re-read your original post and it makes perfect sense.

Reply

Answer 2

Badunit

Level 7

26,006 points

Feb 13, 2013 7:31 AM in response to fred6633

---

Reply

Answer 3

fred6633 Author

Level 1

29 points

Feb 13, 2013 1:00 PM in response to fred6633

I've found out now that Hiroto's script works when Numbers isn't active. If I add "tell application 'Numbers' to activate" at the top of the script, Numbers gets activated and nothing more happens. So I mistakenly thought that something went wrong when I created a service in Automator.

I tested to save Hiroto's script as a workflow. Then I dragged the workflow to the Dock. If Numbers was active, it didn't work. If another application was active, it worked.

Reply

Answer 4

Hiroto

Level 5

7,461 points

Feb 13, 2013 1:38 PM in response to fred6633

Hello

Sorry for late reply.

I just tested the script saved as Automator service under 10.6.5 with a keyboard shortcut definded in System Preferences and it works as intended. I saved the Automator service such that service receives [no input] in [Numbers.app], where [...] is set via drop down menu in Automator workflow editor window. So I don't know why it does not work as is in your environment...

By the way, the following scripts are for page down and page up in sheet pane.

Tested as script under 10.5.8 and also as Automator service with keyboard shortcut under 10.6.5.

Hope this may be of some help,

H

-- sheet page down
(*
    set scroll bar position of sheets area of Numbers 09
    - simulate page down
*)
sheet_page_down()

on sheet_page_down()
    tell application "System Events"
        tell process "Numbers"
            set frontmost to true
            tell window 1
                tell splitter group 1 -- document pane
                    tell splitter group 1 -- side pane
                        tell scroll area 1 -- sheets area
                            if not (exists scroll bar 1) then return -- do nothing if no scroll bar
                            
                            set vy to (get position)'s item 2 -- view positon y including header
                            set vh to (get size)'s item 2 -- view height including header
                            
                            tell outline 1 -- sheets outline list
                                set oy to (get position)'s item 2 -- outline list position y
                                set oh to (get size)'s item 2 -- outline list height
                                tell group 1 -- header row
                                    set hh to (get size)'s item 2 -- header height
                                end tell
                            end tell
                            
                            set vy_ to vy + hh -- position y of view w/o header
                            set vh_ to vh - hh -- height of view w/o header
                            set vy1 to vy_ + vh_ -- page down
                            if vy1 + vh_ > oy + oh then set vy1 to oy + oh - vh_
                            set p to (vy1 - oy) / (oh - vh_) -- scroll bar value in [0.0, 1.0]
                            
                            tell scroll bar 1
                                set value to p
                            end tell
                        end tell
                    end tell
                end tell
            end tell
        end tell
    end tell
end sheet_page_down
-- end of sheet page down

-- sheet page up
(*
    set scroll bar position of sheets area of Numbers 09
    - simulate page up
*)
sheet_page_up()

on sheet_page_up()
    tell application "System Events"
        tell process "Numbers"
            set frontmost to true
            tell window 1
                tell splitter group 1 -- document pane
                    tell splitter group 1 -- side pane
                        tell scroll area 1 -- sheets area
                            if not (exists scroll bar 1) then return -- do nothing if no scroll bar
                            
                            set vy to (get position)'s item 2 -- view positon y including header
                            set vh to (get size)'s item 2 -- view height including header
                            
                            tell outline 1 -- sheets outline list
                                set oy to (get position)'s item 2 -- outline list position y
                                set oh to (get size)'s item 2 -- outline list height
                                tell group 1 -- header row
                                    set hh to (get size)'s item 2 -- header height
                                end tell
                            end tell
                            
                            set vy_ to vy + hh -- view position y w/o header
                            set vh_ to vh - hh -- view height w/o header
                            set vy1 to vy_ - vh_ -- page up
                            if vy1 < oy then set vy1 to oy
                            set p to (vy1 - oy) / (oh - vh_) -- scroll bar value in [0.0, 1.0]
                            
                            tell scroll bar 1
                                set value to p
                            end tell
                        end tell
                    end tell
                end tell
            end tell
        end tell
    end tell
end sheet_page_up
-- end of sheet page up

Reply

Answer 5

Hiroto

Level 5

7,461 points

Feb 13, 2013 2:00 PM in response to Hiroto

Perhaps your keyboard shortcuts may be already used for something else in Numbers, in which case those shortcuts would not trigger the services when Numbers is the active applicatiion.

For your information, I used the following keyboard shortcuts for testing.

top (scroll bar value = 0.0) - command option shift U

bottom (scroll bar value = 1.0) - command option shfit D

page up - command shift U

page down - command shift D

Regards,

H

Reply

Answer 6

fred6633 Author

Level 1

29 points

Feb 13, 2013 2:15 PM in response to Hiroto

Thanks,

I also saved it with no input and the application "Numbers". It couldn't be because the shortcut keys were in use since I first tested the services in the menu. And since nothing happened, I didn't asign them keyboard shortcuts.

As I mentioned in my previous post, when I saves it as a workflow, it doesn't work when Numbers is active. If another application is active, the workflow works.

It's the same on both my iMac and Macbook. Both have 10.6.8. I noticed that you use 10.6.5.

PageUp and PageDown couldn't I get to work even in Script Editor. When I run it, Numbers get activated and then nothing.

Reply

Answer 7

fred6633 Author

Level 1

29 points

Feb 13, 2013 11:59 PM in response to Hiroto

This is very strange, at least to me. I can't get it.

This is the output under "Answer" in Apple Script Editor of your "PageDown" script:

tell application "System Events"

set frontmost of process "Numbers" to true

exists scroll bar 1 of scroll area 1 of splitter group 1 of splitter group 1 of window 1 of process "Numbers"

--> false

end tell

This is the output of your "End "script:

tell application "System Events"

exists scroll bar 1 of scroll area 1 of splitter group 1 of splitter group 1 of window 1 of process "Numbers"

--> true

set value of scroll bar 1 of scroll area 1 of splitter group 1 of splitter group 1 of window 1 of process "Numbers" to 1.0

end tell

If I add "Tell application 'Numbers' to activate" to your "End" script the output is this:

tell application "Numbers"

activate

end tell

tell application "System Events"

exists scroll bar 1 of scroll area 1 of splitter group 1 of splitter group 1 of window 1 of process "Numbers"

--> false

end tell

Your "End" script works when Numbers isn't in front, but when I run it with Numbers in front it doesn't work. I don't get why scroll bar 1 is false as soon as Numbers is frontmost. This must be the explanation why your "home" and "End" script only works when Numbers isn't in front.

All your scripts works, even when Numbers is in front, if I add "activate" under any of the lines "tell process 'Numbers", "tell window 1" etc. When I do so to your "PageDown" I get this output:

tell application "System Events"

set frontmost of process "Numbers" to true

activate

exists scroll bar 1 of scroll area 1 of splitter group 1 of splitter group 1 of window 1 of process "Numbers"

--> true

get position of scroll area 1 of splitter group 1 of splitter group 1 of window 1 of process "Numbers"

--> {387, 115}

get size of scroll area 1 of splitter group 1 of splitter group 1 of window 1 of process "Numbers"

--> {235, 694}

get position of outline 1 of scroll area 1 of splitter group 1 of splitter group 1 of window 1 of process "Numbers"

--> {387, 125}

get size of outline 1 of scroll area 1 of splitter group 1 of splitter group 1 of window 1 of process "Numbers"

--> {220, 1580}

get size of group 1 of outline 1 of scroll area 1 of splitter group 1 of splitter group 1 of window 1 of process "Numbers"

--> {220, 17}

set value of scroll bar 1 of scroll area 1 of splitter group 1 of splitter group 1 of window 1 of process "Numbers" to 0.757475083056

end tell

With this solution "Numbers" gets unfocused. To get it refocused I have to add "Tell application 'Numbers' to activate". Now it flashes a bit but anyway it works.

Thank you very much for your added "PageDown" and "PageUp" scripts.

Best Regards

Fred

Reply

Answer 8

fred6633 Author

Level 1

29 points

Feb 14, 2013 12:32 AM in response to Hiroto

Hello Hiroto,

I found out that I have to change "window 1" to "window 2" to get the scripts to work in Numbers.

"Window 1" works If I run the script outside "Numbers.

Best Regards

Fred

Reply

Answer 9

fred6633 Author

Level 1

29 points

Feb 14, 2013 12:55 AM in response to Hiroto

Hello Hiroto,

It seems that it is the inspector window that becomes "window 1" and the front document becomes "window 2" when I run the scripts inside "Numbers". When I close the Inspector window, your scripts works with "window 1".

I also noticed that the document becomes "window 3" if I have both the Inspector and spellcheck windows open.

Would it be possible to modify the script so it works no matter how many of these "small windows" are open? Many open documents don't seem to be a problem.

Best Regards

Fred

Reply

Answer 10

Hiroto

Level 5

7,461 points

Feb 14, 2013 12:10 PM in response to fred6633

Ah, now I get the picture. Those scripts do not take floating windows into account.

To handle such cases correctly, please replace the following line:

    tell window 1

with this:

    tell (window 1 whose subrole is "AXStandardWindow") -- document window 1

For convenience, here I repost the modified versions of previous scripts and a new one to scroll to the selection if it helps.

1) sheet to home

(*
    set scroll bar position of sheets area of Numbers 09
    - to the top
*)
sheet_to_home()
on sheet_to_home()
    tell application "System Events"
        tell process "Numbers"
            set frontmost to true
            tell (window 1 whose subrole is "AXStandardWindow") -- document window 1
                if not (exists) then return
                tell splitter group 1 -- document pane
                    tell splitter group 1 -- side pane
                        tell scroll area 1 -- sheets area
                            tell scroll bar 1
                                if exists then
                                    set value to 0.0 -- [0.0, 1.0]
                                end if
                            end tell
                        end tell
                    end tell
                end tell
            end tell
        end tell
    end tell
end sheet_to_home

2) sheet to end

(*
    set scroll bar position of sheets area of Numbers 09
    - to the bottom
*)
sheet_to_end()
on sheet_to_end()
    tell application "System Events"
        tell process "Numbers"
            set frontmost to true
            tell (window 1 whose subrole is "AXStandardWindow") -- document window 1
                if not (exists) then return
                tell splitter group 1 -- document pane
                    tell splitter group 1 -- side pane
                        tell scroll area 1 -- sheets area
                            tell scroll bar 1
                                if exists then
                                    set value to 1.0 -- [0.0, 1.0]
                                end if
                            end tell
                        end tell
                    end tell
                end tell
            end tell
        end tell
    end tell
end sheet_to_end

3) sheet page up

(*
    set scroll bar position of sheets area of Numbers 09
    - simulate page up
*)
sheet_page_up()
on sheet_page_up()
    tell application "System Events"
        tell process "Numbers"
            set frontmost to true
            tell (window 1 whose subrole is "AXStandardWindow") -- document window 1
                if not (exists) then return
                tell splitter group 1 -- document pane
                    tell splitter group 1 -- side pane
                        tell scroll area 1 -- sheets area
                            if not (exists scroll bar 1) then return -- do nothing if no scroll bar
                            
                            set vy to (get position)'s item 2 -- view position y including header
                            set vh to (get size)'s item 2 -- view height including header
                            
                            tell outline 1 -- sheets outline list
                                set oy to (get position)'s item 2 -- outline list position y
                                set oh to (get size)'s item 2 -- outline list height
                                tell group 1 -- header row
                                    set hh to (get size)'s item 2 -- header height
                                end tell
                            end tell
                            
                            set vy_ to vy + hh -- view position y w/o header
                            set vh_ to vh - hh -- view height w/o header
                            set vy1 to vy_ - vh_ -- page up
                            if vy1 < oy then set vy1 to oy
                            set p to (vy1 - oy) / (oh - vh_) -- scroll bar value in [0.0, 1.0]
                            
                            tell scroll bar 1
                                set value to p
                                --return p
                            end tell
                        end tell
                    end tell
                end tell
            end tell
        end tell
    end tell
end sheet_page_up

4) sheet page down

(*
    set scroll bar position of sheets area of Numbers 09
    - simulate page down
*)
sheet_page_down()
on sheet_page_down()
    tell application "System Events"
        tell process "Numbers"
            set frontmost to true
            tell (window 1 whose subrole is "AXStandardWindow") -- document window 1
                if not (exists) then return
                tell splitter group 1 -- document pane
                    tell splitter group 1 -- side pane
                        tell scroll area 1 -- sheets area
                            if not (exists scroll bar 1) then return -- do nothing if no scroll bar
                            
                            set vy to (get position)'s item 2 -- view position y including header
                            set vh to (get size)'s item 2 -- view height including header
                            
                            tell outline 1 -- sheets outline list
                                set oy to (get position)'s item 2 -- outline list position y
                                set oh to (get size)'s item 2 -- outline list height
                                tell group 1 -- header row
                                    set hh to (get size)'s item 2 -- header height
                                end tell
                            end tell
                            
                            set vy_ to vy + hh -- position y of view w/o header
                            set vh_ to vh - hh -- height of view w/o header
                            set vy1 to vy_ + vh_ -- page down
                            if vy1 + vh_ > oy + oh then set vy1 to oy + oh - vh_
                            set p to (vy1 - oy) / (oh - vh_) -- scroll bar value in [0.0, 1.0]
                            
                            tell scroll bar 1
                                set value to p
                                --return p
                            end tell
                        end tell
                    end tell
                end tell
            end tell
        end tell
    end tell
end sheet_page_down

5) sheet to selection

(*
    set scroll bar position of sheets area of Numbers 09
    - scroll to show the selection in centre
    
    * This script is too slow to use under 10.5.8. However it's fast enough under 10.6.5
*)
sheet_to_selection()
on sheet_to_selection()
    tell application "System Events"
        tell process "Numbers"
            set frontmost to true
            tell (window 1 whose subrole is "AXStandardWindow") -- document window 1
                if not (exists) then return
                tell splitter group 1 -- document pane
                    tell splitter group 1 -- side pane
                        tell scroll area 1 -- sheets pane
                            if not (exists scroll bar 1) then return -- do nothing if no scroll bar
                            
                            set vy to (get position)'s item 2 -- view position y including header
                            set vh to (get size)'s item 2 -- view height including header
                            
                            tell outline 1 -- sheets outline list
                                set oy to (get position)'s item 2 -- outline list position y
                                set oh to (get size)'s item 2 -- outline list height
                                tell group 1 -- header row
                                    set hh to (get size)'s item 2 -- header height
                                end tell
                                
                                tell (row 1 whose selected is true) -- selected row
                                    set ry to (get position)'s item 2 -- row position y
                                    set rh to (get size)'s item 2 -- row height
                                end tell
                            end tell
                            
                            set vy_ to vy + hh -- view position y w/o header
                            set vh_ to vh - hh -- view height w/o header
                            set vy1 to (ry + rh / 2 - vh_ / 2) -- view position to show selection in centre
                            if vy1 < oy then
                                set vy1 to oy
                            else if vy1 + vh_ > oy + oh then
                                set vy1 to oy + oh - vh_
                            end if
                            set p to (vy1 - oy) / (oh - vh_) -- scroll bar value in [0.0, 1.0]
                            
                            tell scroll bar 1
                                set value to p
                                --return p
                            end tell
                        end tell
                    end tell
                end tell
            end tell
        end tell
    end tell
end sheet_to_selection

All the best,

H

Reply

Answer 11

fred6633 Author

Level 1

29 points

Feb 14, 2013 1:13 PM in response to Hiroto

Thank you so much! Alsro scroll to selection is very useful.

Best Regards from Sweden

Fred

Reply

Answer 12

Hiroto

Level 5

7,461 points

Feb 14, 2013 3:15 PM in response to fred6633

Hello Fred,

My pleasure! I also learnt something. 😉

Best wishes from Japan

Hiroto

Reply

Answer 13

fred6633 Author

Level 1

29 points

Feb 15, 2013 12:02 AM in response to Badunit

That was a good script! I gave it a second look. I have 49 sheets. The best would have been to type a letter and the first sheet that begins with that letter should be selected. But since that is not possible I used your script to select 10th, 20th, 30th and 40th rows. Then I can navigate one row up or down.

I modified it a bit to avoid the selected sheets to be expanded:

tell application "System Events" to tell application process "Numbers"

tell outline 1 of scroll area 1 of splitter group 1 of splitter group 1 of front window

set targetSheetRow to 40th row where ((value of attribute "AXDisclosureLevel" is 0))

tell targetSheetRow to set {value of attribute "AXSelected", value of attribute "AXDisclosing"} to {true, false}

-- Focus the "Sheets" column ('outline 1 …') AFTER the target row is selected.

set value of attribute "AXFocused" to false

delay 0.1

end tell

Reply

Answer 14

fred6633 Author

Level 1

29 points

Feb 15, 2013 1:52 AM in response to fred6633

A tip: I created a service in Automator with first action Badunit's script to select a row and the second action Hiroto's script to scroll to selection. I created four different services: row 10, 20, 30 and 40.

Reply