Hi,
OK, I have not tested with blank cells or numbers, the title of the menu item are not changed in these two cases.
I modified the script to manage blank cells and numbers (assign a value to blank cell and convert number to a string), this doesn't change anything to the formats and values of the selected cells
Also, I modified the script for the minor glitch, I think he missed a delay, I add two delay of 0.1 second, I don't know if that's right, I did not have this issue.
Here is the revised script :
---------------------------
--This script works on multiple selections but only if the selection is contiguous.
settValuestomydoThis(1) -- get values of the selection
iftValuesisnot "" then
activate
display dialog "Select the cells where you want to create or update the PopUp." & return & "After that, click on the 'OK' button."
mydoThis(tValues) -- set the cell format of the new selection to "PopUp Menu" and if necessary then change value of the menu item in popup
tellapplication "Numbers" todisplay dialog "Done"
else
tell application "Numbers" to display dialog "You must select the cells in a table before running this script."
endif
ondoThis(n)
tell application "Numbers"
settTablesto (tablesofsheetsoffrontdocumentwhoseitsselection rangeisnotmissing value)
repeatwithtintTables -- t is a list of tables of a sheet
ifcontentsoftisnot {} then -- this list is not empty, it's the selected sheet
set activeTable to (get item 1 of t)
if n = 1 then
tell (selection range of activeTable)
set origFormat to {}
set blankCells to {}
tell (getcellswhosevalueis 0.0) -- to check blank cell, blank cell = 0.0, but maybe it's a number
repeat with i from 1 to (get count)
tell (get item i)
set oFormat to its format
set its format to text -- change format
if value = 0.0 then -- it's a blank cell, because 0.0 is not a string
set value to "__blank__"
set end of origFormat to oFormat
set end of blankCells to it
else -- it's a number, because 0.0 in text format is "0"
set its format to oFormat
end if
end tell
end repeat
end tell
set tValues to value of cells -- get values of the selection
tell blankCells to repeat with i from 1 to (get count)
tell item i to set {format, value} to {item i of origFormat, ""} -- reset format and value of blank cells
end repeat
returntValues -- ** return values of the selection **
end tell
else
setformatof (cellsofselection rangeofactiveTable) topop up menu -- set the format to pop up menu
returnmysetValuePopUp(n) -- set value of each menu item
end if
end if
end repeat
endtell
return ""
enddoThis
onsetValuePopUp(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" ofinspectorWindow -- raise the Inspector window to the front
end if
delay 0.3
tell window 1
clickradio button 4 ofradio 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
click button 1 -- button [+]
end repeat
delay 0.1
keystrokereturn -- validate the default name of the last menu item
else if tc > lenL then -- ** remove menu items **
set thisRow to row (lenL + 1) of tTable
repeat while exists thisRow
select thisRow
click button 2 -- button [-]
end repeat
end if
delay 0.1
set L2 to value of text field 1 of rows of tTable
repeatwithifrom 1 tolenL -- ** if necessary then change value of the menu item **
set tValue to (item i of L) as string
if (item i of L2) is not tValue then -- not the same value
if tValue = "__blank__" then
set value of text field 1 of row i of tTable to "_" --set value of this menu item to underscore
else
set value of text field 1 of row i of tTable to tValue
end if
end if
end repeat
end tell
end tell
end tell
endtell
endsetValuePopUp
---------------------------
if the cell is empty the menu title will be a underscore character
If you want it to be another character, replace the underscore character in this line ( set value of text field 1 of row i of tTable to "_") at the end of the script.
Don't use a empy string, you can not select any item in the popup menu.
But if you want to ignore the blank cells (to not to add unnecessary menu item in the popup), I can change the script.
patrickcheatham wrote:
Ultimately, I'm after something more dynamic. Would it be possible to have a script similar to this which would update a pop-up?
...I ran the script today, but have added two new rows to my first table in your step 1. I'd like to add the new information from the table to the pop-up(s) -- a total of two new entries in each pop-up?
With the current script, you can, but you must select all the cells, the script will update the menu items that will be different and add additional menu items.
But, here is the script wich append the values of the selection to popup menus, this script skip the blank cells :
---------------------------
--This script works on single selection or (multiple selections but only if the selection is contiguous).
-- this script append the values of the selection to popup menus (another selection)
settValuestomydoThis(1) -- get values of the selection
iftValuesisnot "" then
activate
display dialog "Select the cells where you want to update the PopUp." & return & "After that, click on the 'OK' button."
repeat
mydoThis(tValues) -- append the values to popup menu
if the result is "a cell is not a popup" then
activate
display dialog "The selection contains a cell which is not a popup menu." & return & "Select the cells where you want to update the PopUp." & return & "After that, click on the 'OK' button."
else
tell application "Numbers" to display dialog "Done"
return
end if
endrepeat
else
tell application "Numbers" to display dialog "You must select the cells in a table before running this script."
endif
ondoThis(n)
tell application "Numbers"
settTablesto (tablesofsheetsoffrontdocumentwhoseitsselection rangeisnotmissing value)
repeatwithtintTables -- t is a list of tables of a sheet
ifcontentsoftisnot {} then -- this list is not empty, it's the selected sheet
set activeTable to (get item 1 of t)
if n = 1 then
tell (selection range of activeTable)
set origFormat to {}
set blankCells to {}
tell (getcellswhosevalueis 0.0) -- to check blank cell, blank cell = 0.0, but maybe it's a number
repeat with i from 1 to (get count)
tell (get item i)
set oFormat to its format
set its format to text -- change format
if value = 0.0 then -- it's a blank cell, because 0.0 is not a string
set value to "__blank__"
set end of origFormat to oFormat
set end of blankCells to it
else -- it's a number, because 0.0 in text format is "0"
set its format to oFormat
end if
end tell
end repeat
end tell
set tValues to (value of cells whose value is not "__blank__") -- get values of the selection, skip blank cells
tell blankCells to repeat with i from 1 to (get count)
tell item i to set {format, value} to {item i of origFormat, ""} -- reset format and value of blank cells
end repeat
returntValues -- ** return values of the selection **
end tell
else
set x to (cells of selection range of activeTable whose its format is not pop up menu) -- check other format
if x is {} then
returnmysetValuePopUp(n) -- append these values to the popup menu
else
return "a cell is not a popup"
end if
end if
end if
end repeat
endtell
return ""
enddoThis
onsetValuePopUp(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" ofinspectorWindow -- raise the Inspector window to the front
end if
delay 0.3
tell window 1
clickradio button 4 ofradio 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 tc + (count L)
set j to 1
repeat until (count rows of tTable) = lenL -- ** add menu items **
click button 1 -- button [+]
delay 0.1
set value of text field 1 of last row of tTable to (item j of L) as string
set j to j + 1
end repeat
end tell
end tell
end tell
endtell
return ""
endsetValuePopUp
---------------------------
