I have a selection list in S14.
There is a cell range of S15 to S38.
I want to be able to make a selection from that list, and run a macro that will place the selection into the next empty cell in the range (S15 to S38), and clear the selection list (not delete the selection list).
-
What do you mean by “selection list in S14”? Is there defined a
>Data>ValidityinCell rangemode? You might better use aComboBoxor aListBox. -
Please explain clearly what’s meant by “the next available cell in a range”. What kind of range (row, column, rectangle)? What’s meant by “available”. What shall happen if no “available” cell is contained in the range?
-
Do you intend to create a kind of “history of chosen contents”?
-
What if a cell anywhere in “the range” is newly made “available” by editing?
-
What’s the exact meaning of
“… and clear the selection list (not delete the selection list)” -
In what way did you actually ask for Python code?
Or did you assume “Macro” means Basic code? -
What do you eventually want to achieve?
Are you sure the question isn’t another example for the XY-problem?
- By “Selection list” I mean there is a selection list in S14 defined by Data Validity.
- The next available cell in a range. The range is S15 to S38, so if S15 through S18 has something in it then it will autofill the next cell which is S19.
- If a cell is cleared out then it becomes the “next available cell in the range”, meaning it will be selected if the macro is activated through a button.
- By “clear the selection list” meaning it clears it out by making it blank (there is a blank option in the selection list), and by “not delete the selection list” it leaves the selection list in the S14 cell.
- I did not ask for Python as I expected basic. I assumed that’s what most people use when helping people who don’t know different programming languages. Basic would be easier for me as I am starting to pick up a little.
- I want to achieve is for the end user to fill the range of cells (S15 to S38) by making a selection in S14 then activating the macro by clicking a button.
- Yes, I am sure it’s not an XY problem.
I have found that when asking questions in forums it’s best to keep it short and to the point. If I explain to much then many read into it to much and assume something different than what I wanted, especially when it comes to programming languages. I believe the other person who answered understood what I meant but he posted python code and I don’t know python at all nor have I ever worked with it. Plus I apparently need to install a JRE to run python on my system which can be a hassle.
Hallo
on which Sheet ???
def fill_next_empty(*_):
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.Sheets.Tabelle1 # adapt to your needs
source = sheet['S14'] # ""
cell_range = sheet["S15:S38"] # ""
formula = source.Formula
# queryEmptyCells
# https://www.openoffice.org/api/docs/common/
# ref/com/sun/star/sheet/XCellRangesQuery.html#queryEmptyCells
empties = cell_range.queryEmptyCells()
empties[0][0,0].Formula = formula
source.Formula = ""Is this something that runs automatically or can I place it in a button?
I realize now from the way it sounds that I wanted it to run as soon as a selection was made.
I prefer to run it from a button and not run automatically.
It doesnt run automatically but it seems you need the help of apso.oxt to manage and organize your pythoncode.
after the script is installed you may execute it via →→Tools→Macros→organize pythonscripts→→My Macros → <name>→fil_next_empty or your on your own drawing a button and link the button to the function
On the sheet named “Start”, but is there any chance this can be done in basic instead of python?
of course it can, if you had a minimal clue about Basic, you could translate it yourself, but otherwise with using Apso its a matter of Copy&Paste (you should be able to replace …Tabelle1 with …Start
I wouldn’t claim to have understood everything now, and the answers to my questions-in-return weren’t fully satisfying in my sense.
In addition I generally expect a realistic example file in such a case, and a clear explanation concerning the use-case.
This site calls itself a “Q&A site” and may encourage users to come as “customer only”. Actually we should try to maintain, at least partly, a “Peer to Peer” habit.
Finally: I considered the question again , and reworked a first sketch I had made yesterday slightly. The attached “solution” will not be the final thing for the questioner, but if he (f/m) actually is familiar with programming in the context, she(m/f) will easily adapt it to their needs.
disask95505suggestions2.ods (20.8 KB)
(Still wanting to see a realistic use-case.)
As is often the case, it can be set up more simply:
# should triggered by Sheet-event: »Content changed«
# but reacts only on the OBSERVE -address
OBSERVE = '$C$5'
# and writes to the next empty Cell in:
TARGETADDRESS = '$C$6:$C$40'
def fill_from_drop(event):
if event.AbsoluteName == f"${event.Spreadsheet.Name}.{OBSERVE}":
fillrange = event.Spreadsheet[TARGETADDRESS]
empties = fillrange.queryEmptyCells()
empties[0][0,0].Formula = event.Formula
see attached Example:
fill_from_dropdown_example.ods (14.9 KB)
This is interesting and yet so simple but I don’t want it to be automatic just in case. I want to be able to change the selection if need be and then when I (or the end user) is ready to commit they can hit a button to add it to the range of cells (S15 to S38).
I will try adding your code above to a button to see if it works.
I have been unable to get this to work. The example sheet you posted works great but there is something I am missing when trying this code on my sheet. I first get a BASIC syntax error on the OBSERVE = '$C$5' section. I tried a few different things but nothing seems to work. It might help if I could look at the code you posted in the example sheet but it won’t let me open it.
EDIT: Ah I see why. This is still in Python. I need it in basic.
At this point I may need to remove this thread and start a new one with the premise of using BASIC.
