Insert into multiple selection not possible

Discuss the spreadsheet application
Post Reply
meerakalra
Posts: 104
Joined: Thu Jan 15, 2009 8:18 am

Insert into multiple selection not possible

Post by meerakalra »

Hi all,

I am trying to insert a no in the multiple selected cells. And, the error which I am getting is "Multiple Selection not Possible". Pls can any body tell me how I can try to do this.
OOo 3.0.X on Ms Windows XP
User avatar
squenson
Volunteer
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Insert into multiple selection not possible

Post by squenson »

This is a limitation of Calc and there is not much we can do, unfortunately.
LibreOffice 4.2.3.3. on Ubuntu 14.04
biosavvy
Posts: 1
Joined: Thu May 21, 2009 2:17 pm

Re: Insert into multiple selection not possible

Post by biosavvy »

squenson wrote:This is a limitation of Calc and there is not much we can do, unfortunately.
Sounds more like a usability bug to me. How can we change this functionality? Who to talk to? Why is this a limitation and not a feature?
OOo 3.0.X on OTHER + Ubuntu 9.04, Windows XP
User avatar
TheGurkha
Volunteer
Posts: 6482
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.

Re: Insert into multiple selection not possible

Post by TheGurkha »

biosavvy wrote:
squenson wrote:This is a limitation of Calc and there is not much we can do, unfortunately.
Sounds more like a usability bug to me. How can we change this functionality? Who to talk to? Why is this a limitation and not a feature?
We're not the developers, just users trying to help other users. The developers don't hang out here. To report bugs or make suggestions, you need to register on the issue tracker site and report the issue. Check that there isn't already an open issue on this subject though. Once you have an issue in the system, users can vote for it. Votes are one of the ways that issues are prioritized for fixing/including.

See: [Tutorial] Reporting bugs or suggestions
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
Luiz.siqueira.neto
Posts: 6
Joined: Thu Jul 23, 2009 6:04 pm

Re: Insert into multiple selection not possible

Post by Luiz.siqueira.neto »

If your idea is paste text inside of selected cells than I found a workaround:

1 - Put some character like '*' inside of the cells that you want to paste (most of the times this cells is located on the same column or row than you can use some simple way like select all and past to put the character there);

2 - Select the alternated cells that you want to paste than go to "Edit -> Find & Replace";

3 - Use 'search for' field with the character that you put in step 1 than in 'Replace with' put the text that you want paste and select 'Current selection only' in 'More Options'.

Maybe this looks like a lot of work to do some simple but I use this technique normally to set types of expenses in one step after get large amount of data from my bank, I only open the window 'Find & Replace' once than select and change types many times as necessarily, the 'Replace with' field is in fact a Combo Box with memory (history) this make ease to do the job.

This work very fine for me.

ps: Sorry about my English.

Maybe this can be done with Macros too.
OOo 2.3.X on Mac OSx Leopard + Windows XP
omgfreestuff
Posts: 6
Joined: Fri Nov 16, 2012 10:08 pm

Re: Insert into multiple selection not possible

Post by omgfreestuff »

meerakalra wrote:Hi all,

I am trying to insert a no in the multiple selected cells. And, the error which I am getting is "Multiple Selection not Possible". Pls can any body tell me how I can try to do this.
I know this is old, but I'm posting for anyone searching about this problem like I was.

There may be other situations where this error occurs, but in my case it was simply that I was trying to paste a range from one document to another and the target document didn't have enough rows to accommodate the rows in the clipboard. Inserting a few dozen new rows below the point I wanted to paste at fixed it.

In this case, it is not "a limitation of Calc, not much you can do". It is just a very (unnecessarily) misleading error message that seems like it belongs to another error type entirely.
OpenOffice v3.3.0 on WinXP
osxtra
Posts: 10
Joined: Sat Aug 11, 2018 12:03 pm

Re: Insert into multiple selection not possible

Post by osxtra »

Adding to the previous reply, another thing that could cause this is hidden rows or columns in the target range.

Let's say we have two tabs in our sheet, one named "Raw Data", the other, "Target Data". We want to copy A2:G101 from "Raw Data" into "Target Data", starting at F10 in "Target Data". We're also going to paste special - Formats, Strings, and Numbers (values) - in case the source had any formulas.

Code: Select all

' Copy data from one sheet's tab to another, using Paste Special
Sub TestPasteSpecial

  ' Variables in use
  Dim aPasteArgs(5) As New com.sun.star.beans.PropertyValue
  Dim oController As Object
  Dim oDispatcher As Object
  Dim oDocument As Object
  Dim oFrame As Object
  Dim oTabSource As Object
  Dim oTabTarget As Object
  Dim sRangeSource As String
  Dim sRangeTarget As String
  Dim sTabSource As String
  Dim sTabTarget As String

  ' Set some vars

  ' This is an array used for paste special.
  ' See the section "Copy data between documents"
  '   in Andrew Pitonyak's book "OpenOffice.org Macros Explained"
  ' Element 0's "Value" property controls how paste special works.
  ' Letters can be combined in any order, just include what type of
  '   data you wish to paste:
  ' D for Dates, F for Formulas, S for Strings,
  ' T for Formats, V for Numbers (values)
  aPasteArgs(0).Name = "Flags"
  aPasteArgs(0).Value = "STV"
  aPasteArgs(1).Name = "FormulaCommand"
  aPasteArgs(1).Value = 0
  aPasteArgs(2).Name = "SkipEmptyCells"
  aPasteArgs(2).Value = False
  aPasteArgs(3).Name = "Transpose"
  aPasteArgs(3).Value = False
  aPasteArgs(4).Name = "AsLink"
  aPasteArgs(4).Value = False
  aPasteArgs(5).Name = "MoveMode"
  aPasteArgs(5).Value = 4

  ' Source and target tabs
  sTabSource = "Raw Data"
  sTabTarget = "Target Data"
  
  ' Source range, from which the data will be copied.  Adjust as needed
  sRangeSource = "A2:G101"
  
  ' Target range, to which the copied data will be pasted.  Adjust as needed
  ' (The target range can be set to just the top-left cell where you want to paste)
  sRangeTarget = "F10"
  
  
  ' Some other needed objects
  
  ' An Uno "Helper"
  oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
  
  ' A reference to the active document
  oDocument = ThisComponent
  
  ' Some special internal objects
  oController = oDocument.CurrentController
  oFrame = oController.Frame
  
  ' Objects representing the source and target tabs
  oTabSource = oDocument.getSheets.getByName(sTabSource)
  oTabTarget = oDocument.getSheets.getByName(sTabTarget)

  ' Select the source range
  oController.Select(oTabSource.getCellRangeByName(sRangeSource))
  ' This and other objects could also have been chained in super-verbose mode, i.e.,
  '  ThisComponent.CurrentController.Select(ThisComponent.getSheets.getByName(sTabSource).getCellRangeByName(sRangeSource))

  ' Copy the selection to the clipboard
  oDispatcher.executeDispatch(oFrame, ".uno:Copy", "", 0, Array())

  ' Select the target range
  oController.Select(oTabTarget.getCellRangeByName(sRangeTarget))

  ' Paste Special
  oDispatcher.executeDispatch(oFrame, ".uno:InsertContents", "", 0, aPasteArgs())
  
  ' Deselect (Active cell will be set to top-left of the target range)
  oDispatcher.executeDispatch(oFrame, ".uno:Deselect", "", 0, array())
End Sub
However, this will fail if any rows or columns in the target range are hidden. This is because even if you have enough total rows/columns available for pasting, you can't paste into a hidden row or column, so the range available for pasting is smaller than the source range, triggering the "Insert into multiple selection not possible" crash.

Let's check "Target Data"'s paste range for hidden rows.
Here we are working with rows. This would also work for columns.
I have not found a way to generate and object or array populated with just hidden rows or columns.
It appears you have to iterate the rows/columns and look at each one.

Code: Select all

' Test row visibility
Sub TestRowVisibility
  ' Declare some variables
  Dim bVisibility As Boolean
  Dim i As Integer
  Dim iStart As Integer
  Dim iNumRowsToCheck As Integer
  Dim oRow As Object
  Dim oRows As Object
  Dim sAddress As String
  Dim sTab As String
  
  ' Set the target tab
  sTab = "Target Data"
  
  ' Set the start row (row references are zero-based, so A10 would refer to index 9 in the rows object)
  ' Either adjust it here, or in the loop below.  Here we will adjust it in the loop below.
  iStartRow = 10
  
  ' Set how many rows to check
  iNumRowsToCheck = 100

  ' Create an object of the tab's rows
  oRows = ThisComponent.getSheets().getByName(sTab).getRows

  ' Iterate some rows
  For i = (iStartRow - 1) To (iStartRow - 1 + iNumRowsToCheck)
    ' Create an object of the row
    oRow = oRows.getByIndex(i)

    ' Get the range comprising the row ($'Target Data'.$A$1:$AMJ$1, $'Target Data1'.$A$2:$AMJ$2, etc.)
    sAddress = oRow.AbsoluteName
    
    ' Will be False if the row is hidden, True if it is not hidden
    bVisibility = oRow.IsVisible
  Next
End Sub
If bVisibility in the loop over your target range above is ever False, pasting will definitely crash.
Last edited by robleyd on Tue Oct 08, 2019 12:10 pm, edited 1 time in total.
Reason: Remove green tick
OpenOffice 4.1.5 on OSX
Post Reply