Need to open multiple named ranges. I would like to select the range, run macro and user to give it a ‘name’. Named ranges for use later. On current sheet.
Have searched but not finding, surprisingly, a answer, or maybe I am asking wrong thing.
Thanks
A macro that can not be found, needs to be written. Go ahead.
Objection!
It may also be that experienced users capable of writing useful macros didn’t write a macro somebody searched for because they (correctly) judged that it wouldn’t be useful.
IMO we have a wonderful example of such a case here.
Having selected a CellRange and hitting Alt+SNDS (may differ depending on the UI language) will open a professionally optimized dialog for the task of defining named ranges in an interactive mode. It even checks the intended name while typing goes on for syntactical (forbidden characters e.g.) and semantical (name already used) errors. The only additional action of the user would be to selected a scope if the default (Document, global) isn’t wanted.
The additional options can simply be ignored.
If somebody thinks to need a macro for the unattended creation of named ranges there may be a (very special) reeasonable use-case. He (f/m) should then start studying the API documentation for NamedRanges and NamedRange services.
why not simply ⇒ Sheet ⇒ Named Ranges ⇒ create…
Originally:
Later:
Some terms (header row, anchor cell) are strange or misleading, but concerning the way the name to assign should be determined, there is an explicit contradiction.
... or maybe I am asking wrong thing.
Still my preferred option.
However, using predefined names for the ranges makes a bit more sense than relying on user input.
You may play with this code:
Sub defineSelectionAsNamedRangeWithCurrentSheetAsScopeAndSpecificCellContentAsName()
calcDoc = ThisComponent
currSel = calcDoc.CurrentSelection
If NOT currSel.supportsService("com.sun.star.sheet.SheetCellRange") Then
MsgBox("Only a single (rectangular) range can be named.")
Exit Sub
EndIf
sheet = currSel.Spreadsheet
refCell = currSel.getCellByPosition(0, 0)
preferredName = refCell.String
namedThings = sheet.NamedRanges REM The collection may not only contain ranges.
If namedThings.hasByName(preferredName) Then
MsgBox("An already assigned name can't be used a second time.")
Exit Sub
EndIf
On Local Error Goto fail
namedThings.addNewByName(preferredName, currSel.AbsoluteName, refCell.CellAddress, 0)
Goto done
fail:
MsgBox("Chosen name " & preferredName & " is not accepted.")
done:
End Sub
Look this example:
https://wiki.documentfoundation.org/Macros/Calc/ba019
Sub add_named_range_from_selection()
selection = ThisComponent.CurrentController.Selection
ra = selection.RangeAddress
range_name = selection.SpreadSheet.getCellByPosition(ra.StartColumn, ra.StartRow-1).String
named_ranges = ThisComponent.NamedRanges
If named_ranges.hasByName(range_name) Then
Exit Sub
End If
ca = selection.getCellByPosition(0, 0).CellAddress
named_ranges.addNewByName(range_name, selection.AbsoluteName, ca, 0)
End Sub
Thanks guys. Have tried the link from elmau. Get an exclamation mark with a ref ‘Box403’ ( which is the cell A3 from another sheet !
In the macro should I be able to select a range with cursor, then run the code. At moment I get message above. Selection is done by cursor only and selection range is not listed in code as it is now.
Charlie (M-80) my age = too old!!
If I understood.
Do you want to name the currently selected range?
If so…
How do you determine the name you want for the range?
Hi elmau
The ‘name’ would be from header row in the anchor cell. Top Left of the range. The range will include the header row. Thankyou
Charles
I’m so lazy. Please attach a file with an example and help you with the code.
Hi elmau
Example attached using the macro suggested. Some notes on sheet.
Thanks
NamedLists2.ods (14.9 KB)
Look my first answer.
Thanks for your code. Tried it and get:
line reading: named_ranges.addNewByName(range_name, selection.AbsoluteName, ca, 0)
Message as per att.
What needed to correct.
Thanks
Charles
Hi elmau
Thanks for comments. Invalid name? In the top left header column/row to be used as ‘name’.
The name I have uses letter to start, and no underline, just a number., should this be ok? or do I need all three types in the name? Tried some different combinations but all have the same message as before.
Apologies for not underestanding. As I said old age!
Thankyou
Charles
Thanks elmau
This codes works great. Love it. I am going to play around and add dynamic rows . Learning curve. Will let you know if successful or not!
Charles
Thanks vib.
Will look at it.
Charles
To get rid of my dreams concerning a possibly realistic use-case for automatic range naming, I created an example containing enhanced code:
automaticallyNamingRangesAsPrescribed.ods (18.4 KB)
I will not write a manual.