Alphabetize pop-up menu
Is it possible to alphabetize the entries in a pop-up menu in Numbers? Entering them in alphabetic order isn't possible, since I'll be adding new entries weekly.
Numbers-OTHER
Is it possible to alphabetize the entries in a pop-up menu in Numbers? Entering them in alphabetic order isn't possible, since I'll be adding new entries weekly.
Numbers-OTHER
I submit that the pop-up is not the tool for you as they are not dynamic. You can continue to use the pop-up and you can manually but it will be come intractible. Can you share what you are doing in more detail and maybe we can suggest a solution
Thank you for your answer! I am making a database with suppliers. I used the pop-up menu option to put there the main product they make. I want that in something like a pop-up menu, to make sure that everyone uses the same kind of keywords to describe the main product, since this file will be used by multiple users. I would really appreciate an advice, I truely am a mac noob 😉
Yes, it is like that, but with a lot of main products. I've entered 32 suppliers now and I have 21 different main products.
Hi AKKK,
To take a screen shot, hold down the shift and command keys, then type 4. The cursor will change to crosshairs. Release the shift and command keys. Drag over that part of your screen then release the mouse/trackpad. A screen shot will appear on your desktop. In a reply to a message, click on the camera icon:
and Choose File.
You may have to try this twice. Camera icon sometimes needs a wake-up call, but works the second time.
Regards,
Ian.
Going back to your original question...
It is not possible to rearrange the item list of a popup menu. It is possible to reconstruct the list, either by editing the individual items or by restarting from scratch. But this is a manual operation—each item must be entered individually.
The edits apply only to one menu.
You can paste the new menu into other cells (or into whole rows or columns), but doing so also pastes the current setting of that menu, replacing whatever menu and setting were previously in the cell.
In general, if you're working from top to bottom, you'll want to edit the next menu to be used, then fill down from there, leaving the ones above (where a value has already been chosen) untouched.
Regards,
Barry
Numbers doesn't have a way to alphabetize or sort a pop-up or make it dynamic. But there is a script that can help. It will take a list of items from a Numbers table and will create popup menus from it.
I wish I knew who to attribute the script to. The person's name doesn't appear in the comments and I have forgotten who it was. Wasn't me.
-- Script to populate a Numbers pop-up list.
-- Instructions:
-- GUI scripting must be on in System Preferences
-- Create a list of items. Must be a contiguous range of cells in a table.
-- Select the range of cells to use as items in the popup.
-- Run the script.
-- A dialog box will appear asking you to select which cells you want to turn into pop-ups
-- Select the cells then click OK on the dialog box
set tValues to my doThis(1) -- get values of the selection
if tValues is not "" then
activate
display dialog "Select the cells where you want to create the PopUp." & return & "After that, click on the 'OK' button."
my doThis(tValues) -- set the cell format of the new selection to "PopUp Menu" and set the values of the each menu item
tell application "Numbers" to display dialog "Done"
else
tell application "Numbers" to display dialog "You must select the cells in a table before running this script."
end if
on doThis(n)
tell application "Numbers"
set tTables to (tables of sheets of front document whose its selection range is not missing value)
repeat with t in tTables-- t is a list of tables of a sheet
if contents of t is not {} then -- this list is not empty, it's the selected sheet
set activeTable to (get item 1 of t)
if n = 1 then return value of cells of selection range of activeTable -- return values of the selection
set format of (cells of selection range of activeTable) to pop up menu-- set the format to pop up menu
return my setValuePopUp(n) -- set value of each menu item
end if
end repeat
end tell
return ""
end doThis
on setValuePopUp(L)
tell application "System Events"
tell process "Numbers"
set frontmost to true
delay 0.3
set inspectorWindow to missing value
set tWindows to windows whose subrole is "AXFloatingWindow"
repeat with i in tWindows
if exists radio group 1 of i then
set inspectorWindow to i
exit repeat
end if
end repeat
if inspectorWindow is missing value then
keystroke "i" using {option down, command down} -- Show Inspector
else
performaction "AXRaise" of inspectorWindow-- raise the Inspector window to the front
end if
delay 0.3
tell window 1
clickradio button 4 of radio group 1 -- the "cell format" tab
delay 0.3
tell group 2 of group 1
set tTable to table 1 of scroll area 1
set tc to count rows of tTable
set lenL to (count L)
if tc < lenL then -- ** add menu items **
repeat until (count rows of tTable) = lenL
clickbutton 1 -- button [+]
end repeat
keystrokereturn-- validate the default name of the last menu item
else if tc > lenL then -- ** remove menu items **
repeat while exists row (lenL + 1) of tTable
select row (lenL + 1) of tTable
clickbutton 2 -- button [-]
end repeat
end if
tell tTable to repeat with i from 1 to lenL-- ** change value of each menu item **
set value of text field 1 of row i to item i of L
end repeat
end tell
end tell
end tell
end tell
end setValuePopUp
Useful script!
The same cautions as mentioned in my earlier message apply here. If you've begun using the pop-up menu with the original values, applying the changes to the ones you have used will change the menu AND the item chosen in that menu.
Regards.
Barry
Hello
Honestly I'd avoid pop-up menu in Numbers except for very simple one because it is seriouly limited in funtionality and difficult to manage due to the lack of decent interface.
That's being said, here're three AppleScript scripts you may try to manipulate pop-up menu items in Numbers 09:
1) paragraphs to popup.applescript, which converts multi-line text in each cell in selection range to pop-up menu items in the the cell.
2) popup to paragraphs.applescript, which converts pop-up menu items in each cell in selection range to multi-line text in the cell.
3) sort popup items.applescript, which sorts pop-up menu items in each cell in selection range.
* Note: The last script will use clipboard as temporary storage to get the text of selected item in pop-up menu and thus destroy the current clipboard contents.
Usages are all the same as follows:
Copy each code listed below in a new window of /Applications/Utilities/AppleScript Editor.app, select target range in Numbers 09 and run the script. Scripts are moderately tested with Numbers 2.0.5 under 10.5.8 and 10.6.5 but provided as is without any warranties. As always, please have backup copy of target document before using this sort of scripts.
* In order to use these scripts you need to enbale the option: System Preferences > Universal Access > Enable access for assistive devices.
Hope this may help.
H
# Script 1
--paragraphs to popup.applescript
(*
convert paragraphs to popup menu items
v0.1
*)
_main()
on _main()
script o
property aa : {}
property bb : {}
tell application "Numbers"
activate
-- get selection specfiers
set {range:_range, table:_table} to my _selection(document 1)
-- set pop up menus of cells in selection
if _range is missing value then return
tell _range
set aa to cells
repeat with a in my aa
set a to a's contents
set bb to (a's value as string)'s paragraphs
if (count my bb) > 1 then
set a's format to pop up menu
set _table's selection range to a
my set_popup_items(bb)
end if
end repeat
-- restore selection
set _table's selection range to it
end tell
end tell
end script
tell o to run
end _main
on set_popup_items(dd)
(*
list dd : list of pop up item's string values
*)
tell application "System Events"
tell process "Numbers"
-- bring Numbers to front
set frontmost to true
-- open new inspector window
tell menu bar 1's menu bar item 9 -- view
if menu 1's menu item 11's enabled then
tell menu 1's menu item 11 -- new inspector
click
end tell
else
tell menu 1's menu item 10 -- inspector
click
end tell
end if
end tell
-- get pop up menu items
set c to count dd
tell (window 1 whose subrole = "AXFloatingWindow") -- front inspector window
tell radio group 1
tell radio button 4 -- cell
click
end tell
end tell
tell group 1
tell group 2
tell scroll area 1
tell table 1 -- pop up menu items table
set k to count rows
if k > 0 then select row 1
end tell
end tell
repeat c - k times
click button 1 -- +
end repeat
repeat k - c times
click button 2 -- -
end repeat
tell scroll area 1
tell table 1 -- pop up menu items table
repeat with i from 1 to c
tell row i's text field 1
set focused to true
set value to dd's item i
end tell
end repeat
end tell
end tell
end tell
end tell
tell (button 1 whose subrole = "AXCloseButton") -- close
click
end tell
end tell
end tell
end tell
end set_popup_items
on _selection(doc)
(*
reference doc : target document
return record : {range:_range, table:_table, sheet:_sheet}
_range = reference to named range in selection
_table = table object to which selection range belongs
_sheet = sheet object to which selection range belongs
*)
(*
Limitation
Numbers allows to select uncontinuous regions
but its scripting interface does not provide decent method to retrieve them.
If uncontinuous regions are selected, 'selection range' returns the minimum continuous region
which includes all the regions in selection.
*)
script o
property parent : {}
property pp : {}
local q, r, s, _range, _table, _sheet
tell application "Numbers"
set pp to doc's every sheet's every table's selection range as list
repeat with p in my pp -- per sheet
set q to p's every reference -- retrieve object (filtering out missing value)
if q ≠ {} then
set q to q's item 1 -- selection range object [1]
set r to q as record -- selection range object specifier record [2]
set _table to r's every reference's item 1 -- container table reference [3]
set s to (a reference to _table's selection range) -- selection range reference [4]
set _range to (a reference to _table's range (s's name)) -- named range reference [5]
set _sheet to (_table as record)'s every reference's item 1 -- container sheet reference [3]
return {range:_range, table:_table, sheet:_sheet}
end if
end repeat
return {range:missing value, table:missing value, sheet:missing value}
end tell
(*
[1] class specifier for 'range' is broken in Numbers 09
[2] «class want» value is broken in Numbers 09
[3] simple method to get «class from» value without asking for «class from» key which causes trouble in recompilation of the token 'from'.
[4] proper reference of selection range object
[5] proper reference of named range object
*)
end script
tell o to run
end _selection
on _split(d, t)
local astid, astid0, tt
set astid to a reference to AppleScript's text item delimiters
try
set {astid0, astid's contents} to {astid's contents, {d}}
set tt to t's text items
set astid's contents to astid0
on error errs number errn
set astid's contents to astid0
error errs number errn
end try
return tt
end _split
# Script 2
--popup to paragraphs.applescript
(*
convert popup menu items to paragraphs
v0.1
*)
_main()
on _main()
script o
property aa : {}
property bb : {}
tell application "Numbers"
activate
-- get selection specifiers
set {range:_range, table:_table} to my _selection(document 1)
if _range is not missing value then
tell _range
-- get cells and formats in 1d array
set {aa, bb} to {cells, cells's format}
repeat with i from 1 to count my aa
if my bb's item i = pop up menu then
set a to my aa's item i
set _table's selection range to a -- select cell with pop up menu
set cc to my get_popup_items() -- get pop up menu items
set a's format to text
set a's value to my _join(linefeed, cc)
end if
end repeat
-- restore selection range
set _table's selection range to it
end tell
end if
end tell
end script
tell o to run
end _main
on get_popup_items()
tell application "System Events"
tell process "Numbers"
-- bring Numbers to front
set frontmost to true
-- open new inspector window
tell menu bar 1's menu bar item 9 -- view
if menu 1's menu item 11's enabled then
tell menu 1's menu item 11 -- new inspector
click
end tell
else
tell menu 1's menu item 10 -- inspector
click
end tell
end if
end tell
-- get pop up menu items
tell (window 1 whose subrole = "AXFloatingWindow") -- front inspector window
tell radio group 1
tell radio button 4 -- cell
click
end tell
end tell
tell group 1
tell group 2
tell scroll area 1
tell table 1 -- pop up menu items table
set pp to rows's text field 1's value
end tell
end tell
end tell
end tell
tell (button 1 whose subrole = "AXCloseButton") -- close
click
end tell
end tell
end tell
end tell
return pp
end get_popup_items
on _selection(doc)
(*
reference doc : target document
return record : {range:_range, table:_table, sheet:_sheet}
_range = reference to named range in selection
_table = table object to which selection range belongs
_sheet = sheet object to which selection range belongs
*)
(*
Limitation
Numbers allows to select uncontinuous regions
but its scripting interface does not provide decent method to retrieve them.
If uncontinuous regions are selected, 'selection range' returns the minimum continuous region
which includes all the regions in selection.
*)
script o
property parent : {}
property pp : {}
local q, r, s, _range, _table, _sheet
tell application "Numbers"
set pp to doc's every sheet's every table's selection range as list
repeat with p in my pp -- per sheet
set q to p's every reference -- retrieve object (filtering out missing value)
if q ≠ {} then
set q to q's item 1 -- selection range object [1]
set r to q as record -- selection range object specifier record [2]
set _table to r's every reference's item 1 -- container table reference [3]
set s to (a reference to _table's selection range) -- selection range reference [4]
set _range to (a reference to _table's range (s's name)) -- named range reference [5]
set _sheet to (_table as record)'s every reference's item 1 -- container sheet reference [3]
return {range:_range, table:_table, sheet:_sheet}
end if
end repeat
return {range:missing value, table:missing value, sheet:missing value}
end tell
(*
[1] class specifier for 'range' is broken in Numbers 09
[2] «class want» value is broken in Numbers 09
[3] simple method to get «class from» value without asking for «class from» key which causes trouble in recompilation of the token 'from'.
[4] proper reference of selection range object
[5] proper reference of named range object
*)
end script
tell o to run
end _selection
on _join(d, tt)
local astid, astid0, t
set astid to a reference to AppleScript's text item delimiters
try
set {astid0, astid's contents} to {astid's contents, {d}}
set t to "" & tt
set astid's contents to astid0
on error errs number errn
set astid's contents to astid0
error errs number errn
end try
return t
end _join
# Script 3
--sort popup items.applescript
(*
sort popup menu items
v0.2
v0.2 -
added codes to preserve the selected value of popup menu items.
* this script will destroy the current clipboard contents
*)
_main()
on _main()
script o
property sort_options : {"Sort in ascending order", "Sort in descending order"}
property comparator : {} -- given later
property aa : {}
property bb : {}
property cc : {}
on copy_as_text()
(* this will destroy the current clipboard contents *)
tell application "Numbers" to activate
tell application "System Events"
tell process "Numbers"
keystroke "c" using {command down}
end tell
end tell
delay 0.1
the clipboard as Unicode text
end copy_as_text
tell application "Numbers"
activate
-- get selection specifiers
set {range:_range, table:_table} to my _selection(document 1)
if _range = missing value then return
-- accept sort direction
set r to choose from list sort_options default items sort_options's item 1
if r = false then error number -128 -- user cancel
set r to r's item 1
if r = sort_options's item 1 then
set my comparator to my cmpa
else
set my comparator to my cmpd
end if
tell _range
-- get cells and formats in 1d array
set {aa, bb} to {cells, cells's format}
repeat with i from 1 to count my aa
if my bb's item i = pop up menu then
set a to my aa's item i
set _table's selection range to a -- select cell with pop up menu
set v to my copy_as_text() -- get selected value
set cc to my get_popup_items() -- get pop up menu items
set i1 to my bsearch(cc, v) -- get index of selected value in original list
my msort(comparator, cc) -- sort items
set i2 to my bsearch(cc, v) -- get index of selected value in sorted list
my reorder_popup_items(cc, i2 > i1) -- reorder pop up menu items
end if
end repeat
-- restore selection range
set _table's selection range to it
end tell
end tell
end script
tell o to run
end _main
on get_popup_items()
tell application "System Events"
tell process "Numbers"
-- bring Numbers to front
set frontmost to true
-- open new inspector window
tell menu bar 1's menu bar item 9 -- view
if menu 1's menu item 11's enabled then
tell menu 1's menu item 11 -- new inspector
click
end tell
else
tell menu 1's menu item 10 -- inspector
click
end tell
end if
end tell
-- get pop up menu items
tell (window 1 whose subrole = "AXFloatingWindow") -- front inspector window
tell radio group 1
tell radio button 4 -- cell
click
end tell
end tell
tell group 1
tell group 2
tell scroll area 1
tell table 1 -- pop up menu items table
set pp to rows's text field 1's value
end tell
end tell
end tell
end tell
tell (button 1 whose subrole = "AXCloseButton") -- close
click
end tell
end tell
end tell
end tell
return pp
end get_popup_items
on reorder_popup_items(dd, moved_down)
(*
list dd : list of pop up item's string values
boolean moved_down : whether selected value is moved down in list -- [1]
*)
tell application "System Events"
tell process "Numbers"
-- bring Numbers to front
set frontmost to true
-- open new inspector window
tell menu bar 1's menu bar item 9 -- view
if menu 1's menu item 11's enabled then
tell menu 1's menu item 11 -- new inspector
click
end tell
else
tell menu 1's menu item 10 -- inspector
click
end tell
end if
end tell
-- get pop up menu items
set c to count dd
tell (window 1 whose subrole = "AXFloatingWindow") -- front inspector window
tell radio group 1
tell radio button 4 -- cell
click
end tell
end tell
tell group 1
tell group 2
tell scroll area 1
tell table 1 -- pop up menu items table
set k to count rows
if k > 0 then select row 1
end tell
end tell
repeat c - k times
click button 1 -- +
end repeat
repeat k - c times
click button 2 -- -
end repeat
tell scroll area 1
tell table 1 -- pop up menu items table
if moved_down then -- [1]
repeat with i from c to 1 by -1
tell row i's text field 1
set focused to true
set value to dd's item i
end tell
end repeat
else
repeat with i from 1 to c
tell row i's text field 1
set focused to true
set value to dd's item i
end tell
end repeat
end if
end tell
end tell
end tell
end tell
tell (button 1 whose subrole = "AXCloseButton") -- close
click
end tell
end tell
end tell
end tell
(*
[1] In order to preserve the selected value X, (another) X must exist in popup items when X is changed to other value.
Therefore, when X is moved down by sorting, we need to populate the popup items from bottom to top.
Meanwhile, when X is moved up by sorting, we need to populate the popup items from top to bottom.
*)
end reorder_popup_items
on _selection(doc)
(*
reference doc : target document
return record : {range:_range, table:_table, sheet:_sheet}
_range = reference to named range in selection
_table = table object to which selection range belongs
_sheet = sheet object to which selection range belongs
*)
(*
Limitation
Numbers allows to select uncontinuous regions
but its scripting interface does not provide decent method to retrieve them.
If uncontinuous regions are selected, 'selection range' returns the minimum continuous region
which includes all the regions in selection.
*)
script o
property parent : {}
property pp : {}
local q, r, s, _range, _table, _sheet
tell application "Numbers"
set pp to doc's every sheet's every table's selection range as list
repeat with p in my pp -- per sheet
set q to p's every reference -- retrieve object (filtering out missing value)
if q ≠ {} then
set q to q's item 1 -- selection range object [1]
set r to q as record -- selection range object specifier record [2]
set _table to r's every reference's item 1 -- container table reference [3]
set s to (a reference to _table's selection range) -- selection range reference [4]
set _range to (a reference to _table's range (s's name)) -- named range reference [5]
set _sheet to (_table as record)'s every reference's item 1 -- container sheet reference [3]
return {range:_range, table:_table, sheet:_sheet}
end if
end repeat
return {range:missing value, table:missing value, sheet:missing value}
end tell
(*
[1] class specifier for 'range' is broken in Numbers 09
[2] «class want» value is broken in Numbers 09
[3] simple method to get «class from» value without asking for «class from» key which causes trouble in recompilation of the token 'from'.
[4] proper reference of selection range object
[5] proper reference of named range object
*)
end script
tell o to run
end _selection
on bsearch(xx, x)
(*
list xx : source list
anything x : item to be searched in xx
return integer : the first index of x in xx if {x} is in xx, or 0 if not.
*)
script o
property aa : xx
local i, j, k
if {x} is not in my aa then return 0
set i to 1
set j to count my aa
repeat while j > i
set k to (i + j) div 2
if {x} is in my aa's items i thru k then
set j to k
else
set i to k + 1
end if
end repeat
return i
end script
tell o to run
end bsearch
on cmpa(x, y) -- comparator for msort handler (sort in ascending order)
return x > y
end cmpa
on cmpd(y, x) -- comparator for msort handler (sort in descending order)
return x > y
end cmpd
on msort(cmp_, aa) -- v1.2f2
(*
Basic recursive merge sort handler having list sorted in place
*)
(*
handler cmp_ : comparator
* cmp_(x, y) must return true iff list element x and y are out of order.
list aa : list to be sorted in place
*)
script o
property parent : {} -- limit closure to minimum
property xx : aa -- to be sorted in place
property xxl : count my xx
property yy : {}
property cmp : cmp_
on merge(p, q, r)
(*
property xx: source list
integer p, q, r : absolute indices to specify range to be merged such that
xx's items p thru r is the target range,
xx's items p thru (q-1) is the first sublist,
xx's items q thru r is the second sublist.
(p < q <= r)
*)
local i, j, k, xp, xr, yi, yj, ix, jx
if r - p = 1 then
set xp to my xx's item p
set xr to my xx's item r
if my cmp(xp, xr) then
set my xx's item p to xr
set my xx's item r to xp
end if
return -- exit
else
if p < q - 1 then merge(p, (p + q) div 2, q - 1)
merge(q, (q + r + 1) div 2, r)
end if
(*
At this point, sublits xx[p, q-1] and xx[q, r] have been already sorted (p < q <= r)
*)
if my cmp(my xx's item (q - 1), my xx's item q) then
else -- xx[p, q-1] & xx[q, r] are already sorted
return
end if
set yy to my xx's items p thru r -- working copy for comparison
set ix to q - p
set jx to r - p + 1
set i to 1
set j to q - p + 1
set k to p
set yi to my yy's item i
set yj to my yy's item j
repeat
if my cmp(yi, yj) then
set my xx's item k to yj
set j to j + 1
set k to k + 1
if j > jx then
set my xx's item k to yi
set i to i + 1
set k to k + 1
repeat until k > r
set my xx's item k to my yy's item i
set i to i + 1
set k to k + 1
end repeat
return
end if
set yj to my yy's item j
else
set my xx's item k to yi
set i to i + 1
set k to k + 1
if i > ix then
set my xx's item k to yj
set j to j + 1
set k to k + 1
repeat until k > r
set my xx's item k to my yy's item j
set j to j + 1
set k to k + 1
end repeat
return
end if
set yi to my yy's item i
end if
end repeat
end merge
on _cmp_(x, y)
(* primary comparator *)
return x > y
end _cmp_
if xxl ≤ 1 then return
if cmp_ = {} then set my cmp to _cmp_ -- comparator fallback
my merge(1, (xxl + 2) div 2, xxl)
end script
tell o to run
end msort
Hello
In Script 3, you may want to adjust the delay seconds in _main() :: copy_as_text() handler as follows.
Original:
on copy_as_text()
(* this will destroy the current clipboard contents *)
tell application "Numbers" to activate
tell application "System Events"
tell process "Numbers"
keystroke "c" using {command down}
end tell
end tell
delay 0.1
the clipboard as Unicode text
end copy_as_text
Adjusted:
on copy_as_text()
(* this will destroy the current clipboard contents *)
tell application "Numbers" to activate
tell application "System Events"
tell process "Numbers"
keystroke "c" using {command down}
end tell
end tell
delay 0.2 -- # adjust this as needed
the clipboard as Unicode text
end copy_as_text
I write this because in some circumstances 0.1 sec delay would not be sufficient and script would fail to get the selected item in popup menu, in which case it may fail to preserve the selected item. I'd think 0.2 sec delay is long enough but of course 0.5 sec is safer. This sort of ambiguity is the major drawback of GUI scripting. (There's some delay between the moment of command + c keystroke and the moment of data actually being put in clipboard.)
Regards,
H
Here're revised version of the first 2 scripts which are now to denote the selected menu item as leading tilde in text.
I know these are not directly related to the original question about sorting the menu items, but they may help to edit popup menu items in general.
* Revised script 2a will destroy the current clipborad contents in the same way as the previous script 3.
Regards,
H
# Script 1a
--paragraphs to popup.applescript
(*
convert paragraphs to popup menu items
v0.2
v0.2 -
added code to specify selected item by leading ~ in text.
*)
_main()
on _main()
script o
property mark_char : "~" -- U+007E TILDE
property aa : {}
property bb : {}
on select_popup_item(k)
(*
integer k : index of popup menu item to be selected (k < 0 denotes backward index)
virtual key codes:
kVK_Space = 0x31 (49)
kVK_DownArrow = 0x7D (125)
kVK_UpArrow = 0x7E (126)
*)
tell application "Numbers" to activate
tell application "System Events"
tell process "Numbers"
key code 49 -- space
if k > 0 then
repeat k times
key code 125 -- down arrow
end repeat
else
repeat -k times
key code 126 -- up arrow
end repeat
end if
key code 49 -- space
end tell
end tell
end select_popup_item
tell application "Numbers"
activate
-- get selection specfiers
set {range:_range, table:_table} to my _selection(document 1)
-- set pop up menus of cells in selection
if _range is missing value then return
tell _range
set aa to cells
repeat with a in my aa
set a to a's contents
set bb to (a's value as string)'s paragraphs
set kx to count my bb
if kx > 1 then
-- get index of the first paragraph starting with mark_char
set k to 0
repeat with i from 1 to kx
set b to my bb's item i
if b starts with mark_char then
set k to i
-- remove mark_char
if (count b) > 1 then
set my bb's item i to b's text 2 thru -1
else -- b = mark_char
set my bb's item i to " " -- set b to space
end if
exit repeat
end if
end repeat
-- set popup menu items
set a's format to pop up menu
set _table's selection range to a
my set_popup_items(bb)
-- select item k in popup menu
if k > 0 then
if k > (kx + 1) div 2 then set k to k - kx - 1
my select_popup_item(k)
end if
end if
end repeat
-- restore selection
set _table's selection range to it
end tell
end tell
end script
tell o to run
end _main
on set_popup_items(dd)
(*
list dd : list of pop up item's string values
*)
tell application "System Events"
tell process "Numbers"
-- bring Numbers to front
set frontmost to true
-- open new inspector window
tell menu bar 1's menu bar item 9 -- view
if menu 1's menu item 11's enabled then
tell menu 1's menu item 11 -- new inspector
click
end tell
else
tell menu 1's menu item 10 -- inspector
click
end tell
end if
end tell
-- set pop up menu items
set c to count dd
tell (window 1 whose subrole = "AXFloatingWindow") -- front inspector window
tell radio group 1
tell radio button 4 -- cell
click
end tell
end tell
tell group 1
tell group 2
tell scroll area 1
tell table 1 -- pop up menu items table
set k to count rows
if k > 0 then select row 1
end tell
end tell
repeat c - k times
click button 1 -- +
end repeat
repeat k - c times
click button 2 -- -
end repeat
tell scroll area 1
tell table 1 -- pop up menu items table
repeat with i from 1 to c
tell row i's text field 1
set focused to true
set value to dd's item i
end tell
end repeat
end tell
end tell
end tell
end tell
tell (button 1 whose subrole = "AXCloseButton") -- close
click
end tell
end tell
end tell
end tell
end set_popup_items
on _selection(doc)
(*
reference doc : target document
return record : {range:_range, table:_table, sheet:_sheet}
_range = reference to named range in selection
_table = table object to which selection range belongs
_sheet = sheet object to which selection range belongs
*)
(*
Limitation
Numbers allows to select uncontinuous regions
but its scripting interface does not provide decent method to retrieve them.
If uncontinuous regions are selected, 'selection range' returns the minimum continuous region
which includes all the regions in selection.
*)
script o
property parent : {}
property pp : {}
local q, r, s, _range, _table, _sheet
tell application "Numbers"
set pp to doc's every sheet's every table's selection range as list
repeat with p in my pp -- per sheet
set q to p's every reference -- retrieve object (filtering out missing value)
if q ≠ {} then
set q to q's item 1 -- selection range object [1]
set r to q as record -- selection range object specifier record [2]
set _table to r's every reference's item 1 -- container table reference [3]
set s to (a reference to _table's selection range) -- selection range reference [4]
set _range to (a reference to _table's range (s's name)) -- named range reference [5]
set _sheet to (_table as record)'s every reference's item 1 -- container sheet reference [3]
return {range:_range, table:_table, sheet:_sheet}
end if
end repeat
return {range:missing value, table:missing value, sheet:missing value}
end tell
(*
[1] class specifier for 'range' is broken in Numbers 09
[2] «class want» value is broken in Numbers 09
[3] simple method to get «class from» value without asking for «class from» key which causes trouble in recompilation of the token 'from'.
[4] proper reference of selection range object
[5] proper reference of named range object
*)
end script
tell o to run
end _selection
on _split(d, t)
local astid, astid0, tt
set astid to a reference to AppleScript's text item delimiters
try
set {astid0, astid's contents} to {astid's contents, {d}}
set tt to t's text items
set astid's contents to astid0
on error errs number errn
set astid's contents to astid0
error errs number errn
end try
return tt
end _split
on bsearch(xx, x)
(*
list xx : source list
anything x : item to be searched in xx
return integer : the first index of x in xx if {x} is in xx, or 0 if not.
*)
script o
property aa : xx
local i, j, k
if {x} is not in my aa then return 0
set i to 1
set j to count my aa
repeat while j > i
set k to (i + j) div 2
if {x} is in my aa's items i thru k then
set j to k
else
set i to k + 1
end if
end repeat
return i
end script
tell o to run
end bsearch
# Script 2a
--popup to paragraphs.applescript
(*
convert popup menu items to paragraphs
v0.2
v0.2 -
added code to denote selected item by leading ~ in text.
* this script will destroy the current clipboard contents
*)
_main()
on _main()
script o
property mark_char : "~" -- U+007E TILDE
property aa : {}
property bb : {}
property cc : {}
on copy_as_text()
(* this will destroy the current clipboard contents *)
tell application "Numbers" to activate
tell application "System Events"
tell process "Numbers"
keystroke "c" using {command down}
end tell
end tell
delay 0.2
the clipboard as Unicode text
end copy_as_text
tell application "Numbers"
activate
-- get selection specifiers
set {range:_range, table:_table} to my _selection(document 1)
if _range = missing value then return
tell _range
-- get cells and formats in 1d array
set {aa, bb} to {cells, cells's format}
repeat with i from 1 to count my aa
if my bb's item i = pop up menu then
set a to my aa's item i
set _table's selection range to a -- select cell with pop up menu
set v to my copy_as_text() -- get selected value
set cc to my get_popup_items() -- get pop up menu items
set k to my bsearch(cc, v) -- get index of selected value in original list
if k > 0 then
set my cc's item k to mark_char & my cc's item k -- put leading mark char in selected item
end if
set a's format to text
set a's value to my _join(linefeed, cc)
end if
end repeat
-- restore selection range
set _table's selection range to it
end tell
end tell
end script
tell o to run
end _main
on get_popup_items()
tell application "System Events"
tell process "Numbers"
-- bring Numbers to front
set frontmost to true
-- open new inspector window
tell menu bar 1's menu bar item 9 -- view
if menu 1's menu item 11's enabled then
tell menu 1's menu item 11 -- new inspector
click
end tell
else
tell menu 1's menu item 10 -- inspector
click
end tell
end if
end tell
-- get pop up menu items
tell (window 1 whose subrole = "AXFloatingWindow") -- front inspector window
tell radio group 1
tell radio button 4 -- cell
click
end tell
end tell
tell group 1
tell group 2
tell scroll area 1
tell table 1 -- pop up menu items table
set pp to rows's text field 1's value
end tell
end tell
end tell
end tell
tell (button 1 whose subrole = "AXCloseButton") -- close
click
end tell
end tell
end tell
end tell
return pp
end get_popup_items
on _selection(doc)
(*
reference doc : target document
return record : {range:_range, table:_table, sheet:_sheet}
_range = reference to named range in selection
_table = table object to which selection range belongs
_sheet = sheet object to which selection range belongs
*)
(*
Limitation
Numbers allows to select uncontinuous regions
but its scripting interface does not provide decent method to retrieve them.
If uncontinuous regions are selected, 'selection range' returns the minimum continuous region
which includes all the regions in selection.
*)
script o
property parent : {}
property pp : {}
local q, r, s, _range, _table, _sheet
tell application "Numbers"
set pp to doc's every sheet's every table's selection range as list
repeat with p in my pp -- per sheet
set q to p's every reference -- retrieve object (filtering out missing value)
if q ≠ {} then
set q to q's item 1 -- selection range object [1]
set r to q as record -- selection range object specifier record [2]
set _table to r's every reference's item 1 -- container table reference [3]
set s to (a reference to _table's selection range) -- selection range reference [4]
set _range to (a reference to _table's range (s's name)) -- named range reference [5]
set _sheet to (_table as record)'s every reference's item 1 -- container sheet reference [3]
return {range:_range, table:_table, sheet:_sheet}
end if
end repeat
return {range:missing value, table:missing value, sheet:missing value}
end tell
(*
[1] class specifier for 'range' is broken in Numbers 09
[2] «class want» value is broken in Numbers 09
[3] simple method to get «class from» value without asking for «class from» key which causes trouble in recompilation of the token 'from'.
[4] proper reference of selection range object
[5] proper reference of named range object
*)
end script
tell o to run
end _selection
on _join(d, tt)
local astid, astid0, t
set astid to a reference to AppleScript's text item delimiters
try
set {astid0, astid's contents} to {astid's contents, {d}}
set t to "" & tt
set astid's contents to astid0
on error errs number errn
set astid's contents to astid0
error errs number errn
end try
return t
end _join
on bsearch(xx, x)
(*
list xx : source list
anything x : item to be searched in xx
return integer : the first index of x in xx if {x} is in xx, or 0 if not.
*)
script o
property aa : xx
local i, j, k
if {x} is not in my aa then return 0
set i to 1
set j to count my aa
repeat while j > i
set k to (i + j) div 2
if {x} is in my aa's items i thru k then
set j to k
else
set i to k + 1
end if
end repeat
return i
end script
tell o to run
end bsearch
Alphabetize pop-up menu